Triggers in PLSQL

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;

/