In previous version of database you can control the execution sequence of triggers using FOLLOWS key word when creating triggers.
CREATE OR REPLACE TRIGGER XXX_TRG BEFORE INSERT ON XXX_TABLE_NAME REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW FOLLOWS YYY_TRG DECLARE BEGIN NULL; END;
Oracle database 11g support new feature called compound triggers which can do the same purpose of FOLLOWS but in different manner.
Before dig into compound triggers let’s write about trigger timing first.
The trigger timing is the time when trigger is executed in table i.e (BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW)
Compound trigger can do all previous ti mining in only one compound trigger.
Guidelines for Compound Triggers
a- Compound triggers combine all triggers timing in one trigger body.
b- Compound triggers is only for DML operations and it doesn’t support DDL and system operation.
c- You can use :OLD and :NEW variable identifiers only in ROW level blocks( BEFORE EACH ROW, AFTER EACH ROW )
d- No support of PRAGMA_AUTONOMOUS_TRANSACTION
e- You use WHEN clause to improve the performance of triggers. but no support of WHEN clause in compound triggers.
f- There is one declaration section for all trigger timing which can share variables until finish transaction.
g- Duplicate of trigger timing are permitted in compound trigger.
h- INSERTING, UPDATING and DELETING predicates still available in compound trigger
i- Resolve mutating table error (ORA-04091)
CREATE OR REPLACE TRIGGER XXX_TRG FOR INSERT OR UPDATE OR DELETE ON XXX_TABLE_NAME COMPOUND TRIGGER DECLARE --DECLARATION SECTION FOR ALL TRIGGER TIMING USED BEGIN BEFORE STATEMENT IS NULL; AFTER STATEMENT IS NULL; BEFORE EACH ROW IS NULL; AFTER EACH ROW IS NULL;
INSTEAD OF ROW IS NULL;
Note that BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW and AFTER EACH ROW are optional selection, so you can use what you want regarding your requirements.
INSTEAD OF ROW is used with database views only.
If you describe the structure of USER_TRIGGERS data dictionary view you will find new columns(BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW) related to compound trigger
Compound triggers provide a lot of benefits
1- One location to implement the transaction
2- Whole triggers can share the declaration section
3- Unit of maintenance
4- Resolve mutating table error (ORA-04091)