Oracle 11g Enhancements-Compound Triggers

This feature is part of trigger enhancements released in Oracle 11g Release 1. This particular enhancement paves way to use a single trigger to perform more tasks which could'nt have been possible before.

For example, you can use a single trigger for row triggers as well as row-level triggers. The syntax for a compound trigger is as follows:

CREATE OR REPLACE TRIGGER triggername
  FOR triggeraction ON tablename
  COMPOUND TRIGGER


  -- Variable declaration.


  BEFORE STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE STATEMENT;


  BEFORE EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE EACH ROW;


  AFTER EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER EACH ROW;


  AFTER STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER STATEMENT;


END triggername;
/

Where triggeraction can be INSERT, UPDATE or DELETE.

Example:-

CREATE OR REPLACE TRIGGER t_employee FOR INSERT OR UPDATE OR DELETE ON emp COMPOUND TRIGGER
  l_emp_id emp.empno%type;


  BEFORE STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Statement Trigger');
END IF;
  END BEFORE STATEMENT;
  
  BEFORE EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Each Row Trigger');
END IF;
  END BEFORE EACH ROW;
  
  AFTER EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Each Row Trigger');
END IF;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Statement Trigger');
END IF;
  END AFTER STATEMENT;


END t_emplouee;
/

I don't have a 11g database to test create this script. Just create this trigger and there goes the tension of maintaining multiple triggers.

1 comment :

  1. THANKS! Incredible written code. I tried the similar one. It worked good. All credit goes to you. You made my work easy & simple. Good work!
    sap support costs

    ReplyDelete