oracle compound trigger

What is compound trigger?

A compound trigger combines the following triggers into one single trigger:

• Before statement trigger
• Before row trigger
• After row trigger
• After statement trigger

Advantages of “Compound trigger”?

• Consolidating the individual triggers into single compound trigger
• Global variables to share across timing events
• Effectively load the changes to another table using bulk collect
• To avoid mutating table error

CREATE OR REPLACE TRIGGER emp_comp_trigger
  FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

  g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

  BEFORE STATEMENT IS
    l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
    l_variable VARCHAR2(50) := ' BEFORE EACH ROW ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
    l_variable VARCHAR2(50) := ' AFTER EACH ROW ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    l_variable VARCHAR2(50) := ' AFTER STATEMENT ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END AFTER STATEMENT;

END emp_comp_trigger;
/


CREATE OR REPLACE TRIGGER emp_comp_trigger
  FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

  g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

  BEFORE STATEMENT IS
l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
  BEGIN
if INSERTING then
dbms_output.put_line(l_variable||g_variable||' INSERT ');
elsif UPDATING then
dbms_output.put_line(l_variable||g_variable||' UPDATE ');
elsif DELETING then
dbms_output.put_line(l_variable||g_variable||' DELETE ');
end if;
  END BEFORE STATEMENT;

END emp_comp_trigger;
/