Mutating Table Error in Oracle

Summary: in this tutorial, you will learn about the mutating table error in Oracle and how to fix it using a compound trigger.

When a table is mutating, it is changing. If the change is taking place and you try to make another change in the middle of the first change, Oracle will issue a mutating table error with the error code ORA-04091.

Specifically, the error results from the following operations:

• First, you update data to a table.
• Second, a row-level trigger associated with the table automatically fires and makes another change to the table.

Simulate the mutating table error example

Let’s use the customers table from the sample database for demonstration.

Suppose you want to update the credit limit for a customer. If the credit is greater than 5 times of the lowest non-zero credit, the system automatically assigns this credit to the customer.

CREATE OR REPLACE TRIGGER customers_credit_policy_trg 
    AFTER INSERT OR UPDATE 
    ON customers
    FOR EACH ROW 
DECLARE 
    l_max_credit   customers.credit_limit%TYPE; 
BEGIN 
    -- get the lowest non-zero credit 
    SELECT MIN (credit_limit) * 5 
        INTO l_max_credit 
        FROM customers
        WHERE credit_limit > 0;
    
    -- check with the new credit
    IF l_max_credit < :NEW.credit_limit 
    THEN 
        UPDATE customers 
        SET credit_limit = l_max_credit 
        WHERE customer_id = :NEW.customer_id; 
    END IF; 
END;
/
Code language: SQL (Structured Query Language) (sql)

This statement updates the credit limit of the customer 1 to 12000:

UPDATE customers
SET credit_limit = 12000
WHERE customer_id = 1;
Code language: SQL (Structured Query Language) (sql)

The update action fires the trigger and Oracle issues the following mutating table error:

ORA-04091: table OT.CUSTOMERS is mutating, trigger/function may not see it
Code language: SQL (Structured Query Language) (sql)

As explained earlier, the update statement changes the data of the customers table. The trigger fires and attempts to make another change while the first change is in progress, which results in an error.

Fixing the mutating table error

To fix the mutating table error, you can use a compound trigger if you are using Oracle 11g and later.

Note that if you’re using Oracle 10g or earlier, you need to use a package to fix the mutating table error, which we will not cover in this tutorial.

CREATE OR REPLACE TRIGGER customers_credit_policy_trg    
    FOR UPDATE OR INSERT ON customers    
    COMPOUND TRIGGER     
    TYPE r_customers_type IS RECORD (    
        customer_id   customers.customer_id%TYPE, 
        credit_limit  customers.credit_limit%TYPE    
    );    

    TYPE t_customers_type IS TABLE OF r_customers_type  
        INDEX BY PLS_INTEGER;    

    t_customer   t_customers_type;    

    AFTER EACH ROW IS    
    BEGIN  
        t_customer (t_customer.COUNT + 1).customer_id :=    
            :NEW.customer_id;    
        t_customer (t_customer.COUNT).credit_limit := :NEW.credit_limit;
    END AFTER EACH ROW;    

    AFTER STATEMENT IS    
        l_max_credit   customers.credit_limit%TYPE;    
    BEGIN      
        SELECT MIN (credit_limit) * 5    
            INTO l_max_credit    
            FROM customers
            WHERE credit_limit > 0;

        FOR indx IN 1 .. t_customer.COUNT    
        LOOP                                      
            IF l_max_credit < t_customer (indx).credit_limit    
            THEN    
                UPDATE customers    
                SET credit_limit = l_max_credit    
                WHERE customer_id = t_customer (indx).customer_id;    
            END IF;    
        END LOOP;    
    END AFTER STATEMENT;    
END; 
Code language: SQL (Structured Query Language) (sql)

In this trigger:

• First, declare an array of customer records that includes customer id and credit limit.
• Second, collect affected rows into the array in the row-level trigger.
• Third, update each affected row in the statement-level trigger.

In this tutorial, you have learned about the mutating table error in Oracle and how to fix it using a compound trigger.