Mutation Error

 A mutation error (ORA-04091: table is mutating, trigger/function may not see it) occurs when a row-level trigger tries to read, modify, or query the same table that is currently being modified by the trigger itself.

Why Does Mutation Error Occur?

Oracle prevents a trigger from reading or modifying a table while it is being updated to avoid inconsistent data (also known as a “mutating table”). This happens because:

1. The DML statement is modifying the table.

2. The row-level trigger fires for each row.

3. If the trigger tries to query the same table, Oracle detects an inconsistent state (since the table is still being modified) and raises a mutation error.

Example of Mutation Error

1. Creating a Table

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    name VARCHAR2(50),

    salary NUMBER

);

2. Creating a Row-Level Trigger That Causes Mutation Error

CREATE OR REPLACE TRIGGER trg_employee_salary  

BEFORE UPDATE ON employees  

FOR EACH ROW  

DECLARE  

    v_count NUMBER;  

BEGIN  

    -- Querying the same table inside the trigger (causes mutation error)  

    SELECT COUNT(*) INTO v_count FROM employees WHERE emp_id = :NEW.emp_id;  

END;

/

3. Updating the Table (Causes Error)

UPDATE employees SET salary = 50000 WHERE emp_id = 101;

Error :-

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it

How to Fix Mutation Errors?

1. Use a Compound Trigger (Oracle 11g and Later)

A compound trigger allows you to store temporary values in a global variable and process them in the AFTER STATEMENT section instead of row-level execution.

CREATE OR REPLACE TRIGGER trg_employee_salary  

FOR UPDATE ON employees  

COMPOUND TRIGGER  

    TYPE emp_id_list_t IS TABLE OF employees.emp_id%TYPE INDEX BY PLS_INTEGER;  

    emp_ids emp_id_list_t;  

    BEFORE EACH ROW IS  

    BEGIN  

        -- Store the emp_id in a collection  

        emp_ids(emp_ids.COUNT + 1) := :NEW.emp_id;  

    END BEFORE EACH ROW;  

    AFTER STATEMENT IS  

    BEGIN  

        -- Process the collected emp_ids after all rows are updated  

        FOR i IN 1 .. emp_ids.COUNT LOOP  

            DBMS_OUTPUT.PUT_LINE('Processed EMP_ID: ' || emp_ids(i));  

        END LOOP;  

    END AFTER STATEMENT;  

END trg_employee_salary;  

/

This avoids the mutation error because the AFTER STATEMENT section runs only once after all rows are modified, rather than during each row update.

2. Use an Autonomous Transaction for Logging (Not for Data Modification)

If you only need to log data, you can use an autonomous transaction so that the trigger runs in a separate transaction.

CREATE OR REPLACE TRIGGER trg_employee_salary  

BEFORE UPDATE ON employees  

FOR EACH ROW  

DECLARE  

    PRAGMA AUTONOMOUS_TRANSACTION;  

BEGIN  

    INSERT INTO log_table (emp_id, message) VALUES (:NEW.emp_id, 'Salary updated');  

    COMMIT;  

END;

/

This avoids mutation errors only if the trigger is used for logging. It does not work if the trigger modifies the same table.

3. Use a Statement-Level Trigger Instead of a Row-Level Trigger

If you do not need to process each row individually, you can use a statement-level trigger that fires once for the entire operation.

CREATE OR REPLACE TRIGGER trg_employee_salary  

BEFORE UPDATE ON employees  

BEGIN  

    -- Allowed because it's a statement-level trigger  

    UPDATE employees SET salary = salary * 1.1 WHERE emp_id IN (SELECT emp_id FROM employees);  

END;

/