Example of oracle trigger

The oracle trigger example creates a DML statement-Level trigger. When the INSERT, UPDATE, and DELETE operations are performed on the emp table, it automatically updates the data in the dept_summary table. DDL statements cannot be directly called in PL/SQL blocks. Therefore, EXEC_DDL_STATEMENT in DBMS_UTILITY, an ORACLE built-in package, is used to execute DDL statements to create triggers.

Create table dept_summary (Deptno NUMBER (2), Sal_sum NUMBER (9, 2), Emp_count NUMBER); insert into dept_summary (deptno, sal_sum, emp_count) SELECT deptno, SUM (sal ), COUNT (*) FROM emp group by deptno; -- create a PL/SQL process disp_dept_summary -- call this process in the trigger to display the data in the dept_summary mark. Create or replace procedure disp_dept_summary IS Rec dept_summary % ROWTYPE; CURSOR c1 is select * FROM dept_summary; begin open c1; FETCH c1 into rec; values ('deptno sal_sum emp_count '); DBMS_OUTPUT.PUT_LINE ('-------------------------------------'); WHILE c1 % found loop DBMS_OUTPUT.PUT_LINE (RPAD (rec. deptno, 6) | To_char (rec. sal_sum, '$999,999.99') | LPAD (rec. emp_count, 13); FETCH c1 I NTO rec; end loop; CLOSE c1; END; BEGIN DBMS_OUTPUT.PUT_LINE ('before insert'); Disp_dept_summary (); DBMS_UTILITY.EXEC_DDL_STATEMENT ('create or replace trigger trig1 after insert or delete or update of sal ON emp BEGIN DBMS_OUTPUT.PUT_LINE (''executing trig1 TRIGGER... ''); Delete from dept_summary; insert into dept_summary (deptno, sal_sum, emp_count) SELECT deptno, SUM (sal), COUNT (*) FROM emp group by deptno; END; '); insert into dept (deptno, dname, loc) VALUES (90, 'demo _ dept', 'none _ loc '); insert into emp (ename, deptno, empno, sal) VALUES (USER, 90,999 9, 3000); DBMS_OUTPUT.PUT_LINE ('inserted back'); Disp_dept_summary (); UPDATE emp SET sal = 1000 WHERE empno = 9999; values ('Modified after'); Disp_dept_summary (); delete from emp WHERE empno = 9999; delete from dept WHERE deptno = 90; DBMS_OUTPUT.PUT_LINE ('deleted '); Disp_dept_summary (); DBMS_UTILITY.EXEC_DDL_STATEMENT ('drop TRIGGER trig1 '); exception when others then DBMS_OUTPUT.PUT_LINE (SQLCODE |' --- '| SQLERRM); END;