1. Overview Oracle 11g came up with multiple enhancements for Developers. One of the significant among them was the introduction of Compound Triggers. The article explains the use of Compund Trigges and related notes. 2. Introduction Compound triggers are one of the latest enhancements made in Oracle 11g. They resolve most of the restrictions currently associated with the triggers. It combines all the trigger timings i.e. i.e. before statement and each row, after statement and each row into one compound trigger. 3. Compound Trigger features • Each Trigger time is a separate block in the Trigger body • Compound Trigger supports common state of PL/SQL variables till the execution of an event • Successfully resolves the mutating table error 4. Syntax 4.1. Compound Trigger Structure for Tables Code (Text): CREATE OR REPLACE TRIGGER [TRIGGER NAME] FOR [DML] ON [TABLE NAME] COMPOUND TRIGGER -- Initial section -- Declarations -- Subprograms Optional section BEFORE STATEMENT IS …; Optional section AFTER STATEMENT IS …; Optional section BEFORE EACH ROW IS …; Optional section AFTER EACH ROW IS …; END; 4.2. Compound Trigger Structure for Views Code (Text): CREATE OR REPLACE TRIGGER [TRIGGER NAME] FOR [EVENT TYPE] ON [VIEW NAME] COMPOUND TRIGGER -- Initial section -- Declarations -- Subprograms -- Optional section (exclusive) INSTEAD OF EACH ROW IS ...; END; 5. Compound Trigger Restrictions • Below are the guidelines to use Compound triggers. • Compound Trigger can only behave as DML trigger • Exception must be handled for each event block. • OLD and NEW identifiers can appear in row level timing blocks 6. Mutating Table A Trigger must follow certain rules to maintain database purity. A DML trigger body must not query the affected row of the table or same triggering statement (mutually non exclusive) on the same table, which would result into a recursive triggering event; hence raising the exception ORA-4091. If such situation occurs, the table used is known as Mutating Table. Mutating Table: Illustration Trigger T_GET_SAL fires on update of salary in EMP table and attempt to select salary of employee 104 from EMP table in the body. Now, when an employee record is updated, snapshot of EMP table is floating. As a result o UPDATE event, trigger fires but attempts to query the same EMP table again. In this state, EMP table results into a Mutating table. Code (SQL): CREATE OR REPLACE TRIGGER T_GET_SAL AFTER UPDATE OF SALARY ON EMPLOYEE FOR EACH ROW DECLARE L_GETSAL NUMBER; BEGIN SELECT SALARY INTO L_GETSAL FROM EMPLOYEE WHERE EMPLOYEE_ID =110; … … END; / TRIGGER created. UPDATE EMPLOYEE SET SALARY = SALARY + 1500 WHERE EMPLOYEE_ID = 110; UPDATE EMPLOYEE * ERROR at line 1: ORA-04091: TABLE SCOTT.EMPLOYEE IS mutating, TRIGGER/FUNCTION may NOT see it ORA-06512: at "SCOTT.T_GET_SAL", line 4 ORA-04088: error during execution OF TRIGGER SCOTT.T_GET_SAL' Compound Trigger Solution Code (SQL): CREATE OR REPLACE TRIGGER TRG_GET_SAL FOR INSERT OR UPDATE OF SALARY ON EMPLOYEE COMPOUND TRIGGER TYPE SAL_TYP IS TABLE OF EMPLOYEE.SALARY%TYPE; EMP_SAL SAL_TYP; BEFORE STATEMENT IS BEGIN SELECT SALARY BULK COLLECT INTO EMP_SAL FROM EMPLOYEE WHERE EMPLOYEE_ID = 110; END BEFORE STATEMENT; AFTER EACH ROW IS BEGIN … … END AFTER EACH ROW; END TRG_GET_SAL; SQL> UPDATE EMPLOYEE SET SALARY = SALARY + 1500 WHERE EMPLOYEE_ID = 110; 1 ROW updated. In the above trigger, BEFORE STATEMENT populates the PL/SQL table with the current salary using a SELECT query. Then, in after row trigger, its value can be fetched and the Net increment can be calculated and displayed.