Explain compound triggers in PL/SQL

How it works

A compound trigger combines multiple timing points — BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT — into a single trigger body, sharing state (variables declared in the compound trigger's own DECLARE section) across those sections for the duration of the firing statement. It exists mainly to solve the mutating-table error: accumulate row-level data in a collection during the row-level sections, then run a single set-based operation in the after-statement section, once the table is no longer mutating.

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
  TYPE t_id_tab IS TABLE OF employees.employee_id%TYPE;
  v_changed_ids t_id_tab := t_id_tab();

  BEFORE EACH ROW IS
  BEGIN
    v_changed_ids.EXTEND;
    v_changed_ids(v_changed_ids.COUNT) := :NEW.employee_id;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FORALL v_i IN 1..v_changed_ids.COUNT
      UPDATE salary_history SET last_change_batch = SYSDATE
      WHERE employee_id = v_changed_ids(v_i);
  END AFTER STATEMENT;
END trg_emp_compound;
/

Related Questions