COMPOUND Triggers in Oracle 11G

Let see how to use compound triggers in ORACLE 11G onwards...

Before start with compound trigger, first we will go through the existing trigger flow prior to ORACLE 11G.

Before 11G:
---------------

Create a test table and before statement, before row level, after row level, after statement triggers on the test table created.

SQL> create table triggertest_tab (col1 int);
Table created.
SQL> create or replace trigger triggertest_tab_before_stmt
  2  before insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From before insert stmt level trigger');
  5  end;
  6  /
Trigger created.
SQL> create or replace trigger triggertest_tab_before_row
  2  before insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From before insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_row
  2  after insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From after insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_stmt
  2  after insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From after insert stmt level trigger');
  5  end;
  6
  7  /
Trigger created.


Now, try insert a record into test table

SQL> set serveroutput on;

SQL> insert into triggertest_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

This is how the existing functionality in prior to ORACLE 11G. We have to define triggers at various level separately.

From ORACLE 11G Onwards:
Now, From ORACLE 11G onwards, we can use compound trigger functionality to club one or more triggers into single compound trigger.


Lets create test table and write a compound trigger to have before statement, before row, after row, after statement triggers.

SQL> create table compound_test_tab (col1 int);

Table created.

SQL> create or replace trigger compound_trigger
  2  for insert
  3  on compound_test_tab
  4  compound trigger
  5      before statement is
  6          begin
  7              dbms_output.put_line ('Message From before insert stmt level trigger');
  8          end before statement;
  9      before each row is
 10          begin
 11              dbms_output.put_line ('Message From before insert row level trigger');
 12          end before each row;
 13      after each row is
 14          begin
 15              dbms_output.put_line ('Message From after insert row level trigger');
 16          end after each row;
 17      after statement is
 18          begin
 19              dbms_output.put_line ('Message From after insert stmt level trigger');
 20          end after statement;
 21  end compound_trigger;
 22  /
Trigger created.
SQL>
SQL>

Now, try insert into test table

SQL> insert into compound_test_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

SQL>

This is how we can club multiple trigger levels into single compound trigger.

For more details, Refer Here