Compound Trigger with Example

 
My Tech Channel link:- https://www.youtube.com/oracleshooter
 
 
--==============Insted Of triggers ==========================
 
--Syntax--
 
CREATE OR REPLACE TRIGGER  XX_COMP_EMPLOYEE_TRG
FOR  INSERT OR DELETE OR UPDATE
--OF SAL
ON XX_EMP
COMPOUND TRIGGER
 
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;
 
 
---------- Tables -------
 
select * from  XX_EMP
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10
9001	Manoj	CLERK		08-Nov-20	1000	450	40

 
 
 
 
CREATE OR REPLACE TRIGGER  XX_COMP_EMPLOYEE_TRG
FOR  INSERT OR DELETE OR UPDATE
--OF SAL
ON XX_EMP
COMPOUND TRIGGER
 
   -- Declarative Section (optional) for variables and constants
       
     --Section 1 -- sequence 1 -
     BEFORE STATEMENT IS
     BEGIN
       dbms_output.put_line ('************   Statement Level - BEFORE Trigger Fired *************' );
     END BEFORE STATEMENT;
  
     --Section 2 -- sequence 2 -
     BEFORE EACH ROW IS
     BEGIN
       dbms_output.put_line ('************   Row Level - BEFORE Trigger Fired *************' );
     END BEFORE EACH ROW;
  
     --Section 3 -- sequence 3 -
     AFTER EACH ROW IS
     BEGIN
       dbms_output.put_line ('************   Row Level - AFTER Trigger Fired *************' );
     END AFTER EACH ROW;
  
     --Section 4 -- sequence 4 -
     AFTER STATEMENT IS
     BEGIN
       dbms_output.put_line ('************   Statement Level - AFTER Trigger Fired *************' );
     END AFTER STATEMENT;
 
END XX_COMP_EMPLOYEE_TRG;
 
 
 
 
 
 
CREATE OR REPLACE TRIGGER  XX_COMP_EMPLOYEE_TRG
FOR  INSERT OR DELETE OR UPDATE
--OF SAL
ON XX_EMP
COMPOUND TRIGGER
 
   -- Declarative Section (optional) for variables and constants
       
     --Section 1 -- sequence 1 -
     BEFORE STATEMENT IS
     BEGIN
     dbms_output.put_line ('************   Statement Level - BEFORE Trigger *************' );
         IF INSERTING THEN
         dbms_output.put_line ('INSERT');
         ELSIF UPDATING THEN
         dbms_output.put_line ('UPDATE');
         ELSIF DELETING THEN
         dbms_output.put_line ('DELETE');
         END IF;      
     END BEFORE STATEMENT;
  
     --Section 2 -- sequence 2 -
     BEFORE EACH ROW IS
     BEGIN
       dbms_output.put_line ('************   Row Level - BEFORE Trigger *************' );
       IF INSERTING THEN
         dbms_output.put_line ('INSERT');
         ELSIF UPDATING THEN
         dbms_output.put_line ('UPDATE');
         ELSIF DELETING THEN
         dbms_output.put_line ('DELETE');
         END IF;   
     END BEFORE EACH ROW;
  
     --Section 3 -- sequence 3 -
     AFTER EACH ROW IS
     BEGIN
       dbms_output.put_line ('************   Row Level - AFTER Trigger *************' );
       IF INSERTING THEN
         dbms_output.put_line ('INSERT');
         ELSIF UPDATING THEN
         dbms_output.put_line ('UPDATE');
         ELSIF DELETING THEN
         dbms_output.put_line ('DELETE');
         END IF;   
     END AFTER EACH ROW;
  
     --Section 4 -- sequence 4 -
     AFTER STATEMENT IS
     BEGIN
       dbms_output.put_line ('************   Statement Level - AFTER Trigger *************' );
       IF INSERTING THEN
         dbms_output.put_line ('INSERT');
         ELSIF UPDATING THEN
         dbms_output.put_line ('UPDATE');
         ELSIF DELETING THEN
         dbms_output.put_line ('DELETE');
         END IF;   
     END AFTER STATEMENT;
 
END XX_COMP_EMPLOYEE_TRG;
 
 
--=====================================
 
Example:-
 
select * from XX_EMP
 
insert into XX_EMP values ('9001','Manoj','CLERK','',to_date(sysdate), '1000','450', '40')
 
 
UPDATE  XX_EMP
set SAL = 2000
where EMPNO = '9001'
 
 
DELETE FROM  XX_EMP
where EMPNO = '9001'