Oracle DB 11g New Feature ( Compound Triggers )

In previous post I explained Virtual Column new feature in Oracle Database 11g, you can read it from here
Today I will produce new feature which called Compound Triggers.

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.



Trigger Timing
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)

Compound Trigger Syntax

 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;  

 END;  

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

Conclusion

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)

Thanks
Mahmoud A. El-Sayed