Home > Oracle > Compound Triggers(Oracle DB 11g New Feature )

Compound Triggers(Oracle DB 11g New Feature )

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

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)

Advertisements
Categories: Oracle Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: