Compound Triggers In Oracle 11g

Compound triggers are the database DML triggers which 'compounds' or combines all the triggering timings under one trigger body.
The triggering timings available for database triggers are

• BEFORE STATEMENT 
• AFTER STATEMENT 
• BEFORE EACH ROW
• AFTER EACH ROW

Trigger level variables can be defined which would be visible and accessible in all the timing blocks.
Compound trigger body does not contain any exception section but recommends each block to contain its own exception handler under exception section.

• Trigger firing sequence can be set in Oracle 11g using FOLLOWS keyword
• Compound Triggers to encapsulate multiple triggering timings in one body
• Triggers can be created in ENABLED/DISABLED mode
• The DML triggers in 11g are 25% faster than their earlier versions in terms of compilation, execution and firing according to Oracle.

When to use Compound Triggers:

• Mutating table error ORA-04091
• Multi thread behavior maintains persistent state of session variables till the statement execution finishes. These are defined in the declaration section of the compound trigger.
• Enhanced performance in bulk operations
• Supports encapsulation of multiple program units; thus enhances code interactivity

Compound Triggers Syntax:

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;

Note that none of the timing blocks should be duplicated. Oracle server raises exception PLS-00676 if duplicate timing blocks is found in the compound trigger definition.