PL/SQL Reference VIII - Triggers

===========================================
Triggers Improvement in Oracle 11G
===========================================
Compound Triggers
Disabled Triggers
FOLLOWS clause


===========================================
Compound Triggers
===========================================
Compound Trigger has four timing points:
- BEFORE STATEMENT - before firing the statement
- BEFORE ROW - before row affect
- AFTER ROW - after row affect
- AFTER STATEMENT - after firing the statement

Syntax
CREATE OR REPLACE TRIGGER my_trigger
  FOR DML_event ON my_table
  COMPOUND TRIGGER
  --declarations
  //declarations

  --BEFORE STATEMENT
  BEFORE STATEMENT IS
  BEGIN
    //actions
  END BEFORE STATEMENT;

  --BEFORE EACH ROW
  BEFORE EACH ROW IS
  BEGIN
    //actions
  END BEFORE EACH ROW;

  --AFTER EACH ROW
  AFTER EACH ROW IS
  BEGIN
    //actions
  END AFTER EACH ROW;

  --AFTER STATEMENT

  AFTER STATEMENT IS
  BEGIN
    //actions
  END AFTER STATEMENT;

END my_trigger


Example
Example of using a compound trigger to log changes to ORDERS table to ORDERS_AUD.

CREATE OR REPLACE TRIGGER orders_aud_trg
  FOR INSERT or UPDATE ON ORDERS
  COMPOUND TRIGGER
  --declarations
  limit_const CONSTANT PLS_INTEGER :=7;
  
  TYPE order_totals_type IS TABLE OF orders_aud%ROWTYPE INDEX BY PLS_INTEGER;
  v_order_totals order_totals_type;
  v_counter PLS_INTEGER :=0;
  
  PROCEDURE flush_array_proc IS
    count_elements PLS_INTEGER := v_order_totals.count();
  BEGIN
    FORALL j IN 1..count_elements 
      INSERT INTO ORDERS_AUD VALUES v_order_totals(j);

    v_order_totals.delete();
    v_counter =0;
  END flush_array_proc ;

  --BEFORE STATEMENT
  BEFORE STATEMENT IS
  BEGIN
    v_order_totals.delete();
    v_counter := 0;
  END BEFORE STATEMENT;

  --AFTER EACH ROW

  AFTER EACH ROW IS
  BEGIN
    v_counter := v_counter + 1;
    v_order_totals(v_counter).order_id := :NEW.order_id;
    v_order_totals(v_counter).run_date := SYSDATE;
    v_order_totals(v_counter).user_name := SYS_CONTEXT('userenv',session_user');
    v_order_totals(v_counter).old_total := OLD.order_total;
    v_order_totals(v_counter).new_total := NEW.order_total;
    IF v_order_totals >= limit_const THEN
      flush_array_proc();
    END IF;
  END AFTER EACH ROW;

  --AFTER STATEMENT  

  AFTER STATEMENT IS
  BEGIN
    flush_array_proc();
  END AFTER STATEMENT;

END orders_aud_trg;

===========================================
Disabled Triggers
===========================================
New from Oracle 11.
it is possible to create a trigger in DISABLE state.
This can be useful in case a trigger was created, but cannot be compiled.
Before - The trigger failed at runtime, and the DML to the table failed.
Now - The trigger can be Disabled/Enabled as needed.

CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON some_table FOR EACH ROW DISABLE
BEGIN
  //do something
END;
/

===========================================
FOLLOWS clause
===========================================

Allows to order the execution of triggers relative one to another.

CREATE OR REPLACE TRIGGER my_trigger AFTER UPDATE OF some_column ON some_table FOR EACH ROW FOLLOWS my_another_trigger
BEGIN
  //do something
END;
/