ORA-04091 Table Is Mutating - Trigger Cannot Read or Modify It

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

Error Overview

Error Text: ORA-04091: table name is mutating, trigger/function may not see it

The ORA-04091 error occurs when a row-level trigger attempts to query or modify the same table that fired the trigger. Oracle prevents this to maintain data consistency during the DML operation. This is one of the most common trigger design errors.

Common Causes

1. Row Trigger Querying Own Table

• SELECT on the triggering table in row trigger
• Aggregate functions on the triggering table
• Subqueries referencing the triggering table

2. Trigger Calling Procedures/Functions

• Procedures that query the triggering table
• Functions used in trigger that access the table
• Package procedures with table dependencies

3. Cascading Trigger Effects

• Foreign key cascades causing mutations
• Triggers on multiple related tables
• Recursive trigger chains

4. View Instead-Of Triggers

• Complex views with underlying table access
• Nested view modifications

Diagnostic Queries

Identify Trigger Code

-- View trigger source code
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM user_triggers
WHERE table_name = UPPER('&table_name');

-- Get trigger body
SELECT trigger_name, trigger_body
FROM user_triggers
WHERE table_name = UPPER('&table_name');

-- Full trigger DDL
SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name) as ddl
FROM user_triggers
WHERE table_name = UPPER('&table_name');

Find Dependencies

-- Objects referenced by trigger
SELECT
  name as trigger_name,
  referenced_owner,
  referenced_name,
  referenced_type
FROM user_dependencies
WHERE type = 'TRIGGER'
  AND referenced_name = UPPER('&table_name');

-- All trigger dependencies
SELECT *
FROM user_dependencies
WHERE name IN (
  SELECT trigger_name
  FROM user_triggers
  WHERE table_name = UPPER('&table_name')
);

Resolution Steps

Solution 1: Use Compound Triggers (11g+)

Compound triggers are the recommended solution for mutating table errors:

-- Example: Enforce business rule across all rows
CREATE OR REPLACE TRIGGER emp_salary_check_trg
FOR INSERT OR UPDATE OF salary ON employees
COMPOUND TRIGGER

  -- Package-level collection to store affected departments
  TYPE t_dept_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  g_dept_list t_dept_list;
  g_index PLS_INTEGER := 0;

-- BEFORE STATEMENT: Initialize
BEFORE STATEMENT IS
BEGIN
  g_dept_list.DELETE;
  g_index := 0;
END BEFORE STATEMENT;

-- BEFORE EACH ROW: Collect departments being modified
BEFORE EACH ROW IS
BEGIN
  g_index := g_index + 1;
  g_dept_list(g_index) := :NEW.department_id;
END BEFORE EACH ROW;

-- AFTER STATEMENT: Validate business rules
AFTER STATEMENT IS
  v_total_salary NUMBER;
  v_budget NUMBER;
BEGIN
  FOR i IN 1..g_dept_list.COUNT LOOP
    -- Now safe to query the table - all row operations complete
    SELECT SUM(salary) INTO v_total_salary
    FROM employees
    WHERE department_id = g_dept_list(i);

    SELECT budget INTO v_budget
    FROM departments
    WHERE department_id = g_dept_list(i);

    IF v_total_salary > v_budget THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Total salary exceeds budget for dept ' || g_dept_list(i));
    END IF;
  END LOOP;
END AFTER STATEMENT;

END emp_salary_check_trg;
/

Solution 2: Statement-Level Trigger with Package

For pre-11g databases or simpler scenarios:

-- Step 1: Create package to store affected rows
CREATE OR REPLACE PACKAGE emp_pkg AS
  TYPE t_emp_rec IS RECORD (
    emp_id employees.employee_id%TYPE,
    dept_id employees.department_id%TYPE,
    salary employees.salary%TYPE
  );
  TYPE t_emp_tab IS TABLE OF t_emp_rec INDEX BY PLS_INTEGER;

  g_emp_changes t_emp_tab;
  g_change_count PLS_INTEGER := 0;

  PROCEDURE init;
  PROCEDURE add_change(p_emp_id NUMBER, p_dept_id NUMBER, p_salary NUMBER);
  PROCEDURE validate_changes;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE init IS
  BEGIN
    g_emp_changes.DELETE;
    g_change_count := 0;
  END init;

  PROCEDURE add_change(p_emp_id NUMBER, p_dept_id NUMBER, p_salary NUMBER) IS
  BEGIN
    g_change_count := g_change_count + 1;
    g_emp_changes(g_change_count).emp_id := p_emp_id;
    g_emp_changes(g_change_count).dept_id := p_dept_id;
    g_emp_changes(g_change_count).salary := p_salary;
  END add_change;

  PROCEDURE validate_changes IS
    v_total NUMBER;
  BEGIN
    FOR i IN 1..g_change_count LOOP
      SELECT SUM(salary) INTO v_total
      FROM employees
      WHERE department_id = g_emp_changes(i).dept_id;
      -- Validation logic here
    END LOOP;
    init; -- Clean up
  END validate_changes;
END emp_pkg;
/

-- Step 2: Create triggers
CREATE OR REPLACE TRIGGER emp_before_stmt_trg
BEFORE INSERT OR UPDATE ON employees
BEGIN
  emp_pkg.init;
END;
/

CREATE OR REPLACE TRIGGER emp_row_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
  emp_pkg.add_change(:NEW.employee_id, :NEW.department_id, :NEW.salary);
END;
/

CREATE OR REPLACE TRIGGER emp_after_stmt_trg
AFTER INSERT OR UPDATE ON employees
BEGIN
  emp_pkg.validate_changes;
END;
/

Solution 3: Autonomous Transaction (Use Carefully)

Only for logging/auditing - NOT for validation:

-- ONLY use for non-critical logging, NOT for validation
CREATE OR REPLACE TRIGGER emp_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit_log (
    action_date, action_type, emp_id, old_salary, new_salary
  ) VALUES (
    SYSDATE,
    CASE
      WHEN INSERTING THEN 'INSERT'
      WHEN UPDATING THEN 'UPDATE'
      ELSE 'DELETE'
    END,
    NVL(:NEW.employee_id, :OLD.employee_id),
    :OLD.salary,
    :NEW.salary
  );
  COMMIT;  -- Commits only the audit record
END;
/

Warning: Autonomous transactions commit independently. Don’t use them for validation that must be part of the main transaction.

Solution 4: Redesign Using Constraints

Sometimes triggers aren’t needed:

-- Instead of trigger for uniqueness, use constraint
ALTER TABLE employees ADD CONSTRAINT emp_email_uk UNIQUE (email);

-- Instead of trigger for check, use check constraint
ALTER TABLE employees ADD CONSTRAINT emp_salary_chk
CHECK (salary > 0 AND salary < 1000000);

-- Instead of trigger for referential integrity, use FK
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id) REFERENCES departments(department_id);

Prevention Strategies

1. Trigger Design Guidelines

Do	Don’t
Use compound triggers for complex logic	Query triggering table in row triggers
Use statement-level triggers when possible	Use autonomous transactions for validation
Use constraints when applicable	Create circular trigger dependencies
Keep trigger logic simple	Call complex packages from row triggers

2. Testing Template

-- Test for mutating table before deployment
DECLARE
  v_error_count NUMBER := 0;
BEGIN
  -- Test INSERT
  BEGIN
    INSERT INTO test_table VALUES (test_data);
    ROLLBACK;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -4091 THEN
        v_error_count := v_error_count + 1;
        DBMS_OUTPUT.PUT_LINE('Mutating error on INSERT');
      END IF;
  END;

  -- Test UPDATE
  BEGIN
    UPDATE test_table SET col = value WHERE condition;
    ROLLBACK;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -4091 THEN
        v_error_count := v_error_count + 1;
        DBMS_OUTPUT.PUT_LINE('Mutating error on UPDATE');
      END IF;
  END;

  IF v_error_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Found ' || v_error_count || ' mutating table issues');
  END IF;
END;
/

3. Compound Trigger Template

-- Reusable compound trigger template
CREATE OR REPLACE TRIGGER table_name_trg
FOR INSERT OR UPDATE OR DELETE ON table_name
COMPOUND TRIGGER

  -- Declarations
  TYPE t_rowid_tab IS TABLE OF ROWID INDEX BY PLS_INTEGER;
  g_affected_rows t_rowid_tab;
  g_row_count PLS_INTEGER := 0;

BEFORE STATEMENT IS
BEGIN
  g_affected_rows.DELETE;
  g_row_count := 0;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
  -- Row-level logic that doesn't query the table
  NULL;
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
  g_row_count := g_row_count + 1;
  g_affected_rows(g_row_count) := :NEW.ROWID;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
  -- Now safe to query the table
  FOR i IN 1..g_row_count LOOP
    -- Process each affected row
    NULL;
  END LOOP;
END AFTER STATEMENT;

END table_name_trg;
/

Related Errors

• ORA-04088 - Error during execution of trigger
• ORA-04090 - Table string is mutating (ORA-04091 variant)
• ORA-00036 - Maximum recursive SQL levels exceeded
• ORA-06512 - At line (PL/SQL stack trace)

Emergency Response

Disable Problematic Trigger

-- Immediately disable trigger
ALTER TRIGGER trigger_name DISABLE;

-- Re-enable after fix
ALTER TRIGGER trigger_name ENABLE;

-- Check trigger status
SELECT trigger_name, status FROM user_triggers
WHERE table_name = UPPER('&table_name');

Quick Compound Trigger Conversion

-- Convert row trigger to compound trigger structure
-- Original problem trigger (DON'T DO THIS):
/*
CREATE TRIGGER bad_trigger
AFTER UPDATE ON employees FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;  -- MUTATING!
END;
*/

-- Fixed compound trigger:
CREATE OR REPLACE TRIGGER fixed_trigger
FOR UPDATE ON employees
COMPOUND TRIGGER

AFTER STATEMENT IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;  -- Now safe!
  -- Use v_count
END AFTER STATEMENT;

END fixed_trigger;
/