Triggers in PL/SQL:-
-----------------
Trigger is one of the Database Objects which is saved in database. Triggers are automatically or implicitly executed for any DML operation on the table.
Trigger has two functions ':Old' is used to get old values from the column and ':New' is used to get new values from the column
Trigger of are used for two purposes :-
------------------------------------
To maintain the data uniformity in a business :-
Data is inserted into the database server in any case as upper, lower and mixed case. But the business people will maintain the business data in only one case. Solution is before the data enters into the table as a record this data is taken by a trigger program by our defined trigger logic.
To maintain audit information of table data :-
A table contains millions of records its difficult to keep the track on all inserted, updated and deleted rows. Inorder to maintain the track of all the changes in data an audit table is maintained as a solution. Audit tables will be automatically updated at the time of inserts or deletes. Audit tables are maintained seperately for inserts and updates happen in a table.
Syntax :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Triggers are of 3 types :-
- DDL Triggers : Whenever we perform any ddl operations such as create, drop table then we use ddl triggers.
- DML Triggers : Whenever we perform any dml operations such as insert, update and delete then we use dml triggers.
- Instead of Triggers : If we want to perform any dml operations on view then we use instead of triggers.
Triggers are further classified as :-
- Row Level Trigger : If 100 records are updated in a table then the trigger will be executed 100 number of times then its a Row Level Trigger.
- Statement Level Trigger : If you delete records and if the trigger executed only one time then its a Table Level Trigger.
DML TRIGGERS :-
ROW LEVEL TRIGGER :-
-------------------------
CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET
CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:OLD.EMPNO, :OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO,:OLD.DATEOFENTRY);
END;
/
CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE INSERTED ROWS INTO TARGET
CREATE OR REPLACE TRIGGER UPDATE_INSERT
AFTER INSERT ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:NEW.EMPNO, :NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEPTNO,:NEW.DATEOFENTRY);
END;
/
CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET
CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
INSERT INTO OLDCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.ADDRESS, :OLD.SALARY);
END;
/
CREATE OR REPLACE TRIGGER SALARY_UPDATE_CHECK
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
IF
:NEW.SAL < :OLD.SAL
THEN
RAISE_APPLICATION_ERROR (-20215,'UPDATED SALARY CANNOT BE LESSER THAN CURRENT SALARY');
END IF;
END;
/
STATEMENT LEVEL TRIGGER :-
---------------------------
CREATE TRIGGER STMT_LEVEL_TRIGGER
AFTER UPDATE ON HIGHSCHOOLER
BEGIN
UPDATE HIGHSCHOOLER
SET GRADE = NULL
WHERE GRADE = 13;
END;
CREATE OR REPLACE TRIGGER T1
AFTER INSERT ON STUDENT
BEGIN
UPDATE STUDENT
SET TOT = M1+M2+M3,
PCT = ( M1+M2+M3)/3,
RESULT = CASE WHEN M1 >=35 AND M2 >=35 AND M3 >=35 THEN 'PASS' ELSE 'FAIL' END;
END;
CREATE OR REPLACE TRIGGER RESTRICTED_INSERT
BEFORE INSERT ON EMP
BEGIN
IF
(TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '10:00' AND '18:00')
THEN
RAISE_APPLICATION_ERROR (-20123, 'YOU CAN ADD EMPLOYEE ONLY BETWEEN 10:00 AM AND 6:00 PM.');
END IF;
END;
/
DDL TRIGGERS :-
STATEMENT LEVEL TRIGGER :-
---------------------------------
CREATE OR REPLACE TRIGGER RESTRICT_DROP_TABLE
BEFORE DROP ON DATABASE
BEGIN
RAISE_APPLICATION_ERROR (-20125, 'CANNOT DROP TABLE FROM THIS DATABASE');
END;
/
CREATE OR REPLACE TRIGGER LOGIN_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO LOGIN DETAILS VALUES (USER, SYSDATE, 'LOGIN');
END;
/
CREATE OR REPLACE TRIGGER LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO LOGOFF_DETAILS VALUES (USER, SYSDATE, 'LOGOUT');
END;
/
