Oracle ORA-04091 Error: Causes and Solutions Complete Guide

ORA-04091: Table is Mutating, Trigger/Function May Not See It

ORA-04091 occurs when a row-level trigger attempts to read from or modify the same table that fired the trigger while it is still in the middle of a DML operation (INSERT, UPDATE, or DELETE). Oracle blocks this access to protect data consistency, since the table is in an intermediate, unpredictable state during the operation. This error is one of the most common trigger-related issues Oracle DBAs encounter in production environments.

---

Top 3 Causes

1. Row-Level Trigger Querying Its Own Table

The most frequent cause: a FOR EACH ROW trigger tries to SELECT from the table it's attached to.

-- This trigger will throw ORA-04091
CREATE OR REPLACE TRIGGER trg_bad_example
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    -- ERROR: Querying the mutating table 'employees'
    SELECT COUNT(*)
      INTO v_count
      FROM employees
     WHERE department_id = :NEW.department_id;
END;
/

2. Row-Level Trigger Modifying Its Own Table

Attempting an UPDATE or DELETE on the trigger's own table inside a row-level trigger also causes ORA-04091.

-- This trigger will also throw ORA-04091
CREATE OR REPLACE TRIGGER trg_bad_update
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- ERROR: Modifying the mutating table 'orders'
    UPDATE orders
       SET status = 'PROCESSED'
     WHERE order_id = :NEW.order_id;
END;
/

3. Cascading Triggers Looping Back to the Original Table

When a chain of triggers ultimately accesses the original mutating table, ORA-04091 is raised even if each individual trigger looks harmless in isolation.

-- Trigger A on TABLE_A updates TABLE_B
-- Trigger B on TABLE_B then reads TABLE_A -> ORA-04091
CREATE OR REPLACE TRIGGER trg_cascade_problem
AFTER UPDATE ON table_b
FOR EACH ROW
BEGIN
    -- ERROR: table_a is still mutating from the original DML
    SELECT COUNT(*) INTO :NEW.ref_count FROM table_a;
END;
/

---

Quick Fix Solutions

Fix 1: Use a Compound Trigger (Recommended — Oracle 11g+)

Compound triggers let you collect row-level data and process it after the statement completes, safely avoiding the mutating table issue.

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER

    TYPE t_dept_tab IS TABLE OF employees.department_id%TYPE;
    v_depts t_dept_tab := t_dept_tab();

    AFTER EACH ROW IS
    BEGIN
        -- Collect data only, no table query here
        v_depts.EXTEND;
        v_depts(v_depts.LAST) := :NEW.department_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_total NUMBER;
    BEGIN
        -- Safe to query the table now — DML is complete
        FOR i IN 1 .. v_depts.COUNT LOOP
            SELECT SUM(salary) INTO v_total
              FROM employees
             WHERE department_id = v_depts(i);

            IF v_total > 500000 THEN
                RAISE_APPLICATION_ERROR(-20001, 'Salary limit exceeded.');
            END IF;
        END LOOP;
    END AFTER STATEMENT;

END trg_emp_compound;
/

Fix 2: Package Variable + Statement-Level Trigger (Oracle 10g and below)

Store values in a package-level collection during the row trigger, then process them in a separate statement-level trigger.

-- Package to hold temporary data
CREATE OR REPLACE PACKAGE pkg_trigger_helper AS
    TYPE t_id_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_ids   t_id_list;
    g_count PLS_INTEGER := 0;
END pkg_trigger_helper;
/

-- Row-level trigger: only store data
CREATE OR REPLACE TRIGGER trg_row_level
AFTER UPDATE ON employees FOR EACH ROW
BEGIN
    pkg_trigger_helper.g_count := pkg_trigger_helper.g_count + 1;
    pkg_trigger_helper.g_ids(pkg_trigger_helper.g_count) := :NEW.employee_id;
END;
/

-- Statement-level trigger: safe to query the table
CREATE OR REPLACE TRIGGER trg_stmt_level
AFTER UPDATE ON employees
BEGIN
    FOR i IN 1 .. pkg_trigger_helper.g_count LOOP
        INSERT INTO audit_log (emp_id, log_date)
        VALUES (pkg_trigger_helper.g_ids(i), SYSDATE);
    END LOOP;
    pkg_trigger_helper.g_count := 0;
    pkg_trigger_helper.g_ids.DELETE;
END;
/

Fix 3: Autonomous Transaction (Audit/Logging Only)

Use PRAGMA AUTONOMOUS_TRANSACTION when the trigger only needs to write to a separate logging table and data consistency with the parent transaction is not critical.

CREATE OR REPLACE TRIGGER trg_audit_only
AFTER UPDATE ON employees FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO emp_change_log (emp_id, old_sal, new_sal, changed_at)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
    COMMIT;
END;
/

⚠️ Warning: Never use PRAGMA AUTONOMOUS_TRANSACTION for business logic triggers. It commits independently of the parent transaction, which can lead to data inconsistency.

---

Prevention Tips

1. Default to Compound Triggers for All New Triggers
Establish a team coding standard that all new triggers on Oracle 11g+ must be written as compound triggers. Include a check for row-level self-referencing queries in your code review checklist.

2. Move Complex Logic Out of Triggers
Triggers should be lightweight. Any logic that requires querying or modifying multiple related tables should live in stored procedures or the application layer — not in triggers. Keeping triggers simple dramatically reduces the chance of hitting ORA-04091 in production.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.