Compound Triggers: Solving the Mutating Table Problem Elegantly

The Mutating Table Problem Explained

If you have worked with Oracle row level triggers, you have likely encountered the dreaded ORA-04091: table is mutating error. This error occurs when a row level trigger on a table tries to query or modify the same table that fired the trigger. Oracle prevents this because allowing it could produce unpredictable results, as the table’s data is in an intermediate state during the DML operation.

Before compound triggers existed, the standard workaround involved creating a package with a collection variable, populating it in the row level trigger, and then processing the collected data in an AFTER STATEMENT trigger. This approach worked but required three separate objects (a package, a row trigger, and a statement trigger) and was tedious to write and maintain.

Compound Triggers to the Rescue

Introduced in Oracle 11g, compound triggers combine all four trigger timing points into a single trigger body. They share a common declaration section, which means variables declared at the top are visible to all timing sections. This eliminates the need for a separate package to bridge between row level and statement level processing.

CREATE OR REPLACE TRIGGER trg_audit_salary_changes
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

   -- Shared declaration section
   TYPE t_salary_change IS RECORD (
      employee_id   employees.employee_id%TYPE,
      old_salary    employees.salary%TYPE,
      new_salary    employees.salary%TYPE,
      change_date   TIMESTAMP DEFAULT SYSTIMESTAMP
   );

   TYPE t_changes_tab IS TABLE OF t_salary_change INDEX BY PLS_INTEGER;
   g_changes t_changes_tab;
   g_index   PLS_INTEGER := 0;

   -- BEFORE STATEMENT: initialization
   BEFORE STATEMENT IS
   BEGIN
      g_changes.DELETE;
      g_index := 0;
   END BEFORE STATEMENT;

   -- AFTER EACH ROW: collect changes (cannot query employees here)
   AFTER EACH ROW IS
   BEGIN
      g_index := g_index + 1;
      g_changes(g_index).employee_id := :NEW.employee_id;
      g_changes(g_index).old_salary  := :OLD.salary;
      g_changes(g_index).new_salary  := :NEW.salary;
   END AFTER EACH ROW;

   -- AFTER STATEMENT: process collected changes (safe to query employees now)
   AFTER STATEMENT IS
      l_department_avg NUMBER;
   BEGIN
      FOR i IN 1 .. g_changes.COUNT LOOP
         -- Now we CAN safely query the employees table
         SELECT AVG(salary)
           INTO l_department_avg
           FROM employees
          WHERE department_id = (
             SELECT department_id
               FROM employees
              WHERE employee_id = g_changes(i).employee_id
          );

         INSERT INTO salary_audit(
            audit_id, employee_id, old_salary, new_salary,
            department_avg_after, change_date
         ) VALUES (
            salary_audit_seq.NEXTVAL,
            g_changes(i).employee_id,
            g_changes(i).old_salary,
            g_changes(i).new_salary,
            l_department_avg,
            g_changes(i).change_date
         );
      END LOOP;
   END AFTER STATEMENT;

END trg_audit_salary_changes;
/

Why This Pattern Works

The AFTER EACH ROW section fires for every affected row and collects the data you need into the shared collection. At this point, the table is still mutating, so you cannot query it. The AFTER STATEMENT section fires once after all rows have been processed and the table is no longer mutating. At this point you can safely query the table and perform any additional DML. The shared collection bridges the gap between these two phases.

Practical Applications

Beyond solving the mutating table problem, compound triggers are excellent for aggregating row level data for batch processing. Instead of inserting one audit row per modified record, you could accumulate changes and insert them in bulk using FORALL in the AFTER STATEMENT section, which is far more efficient for large multi row updates.

Version Considerations

Compound triggers were introduced in Oracle 11g Release 1 and are fully supported through 23ai. In Oracle 12c, a bug was fixed that could cause the shared state to be incorrectly reinitialized in certain recursive trigger scenarios. If you are running 11g and experiencing unexpected behavior with compound triggers in complex trigger chains, upgrading to 12c or later resolves most known issues. Oracle 19c through 23ai provide stable, well tested compound trigger support with no significant behavioral changes between versions.