Home Privacy Policy Feedback Link to us Site Map

Oracle/PLSQL: ORA-04091 Error

Error:

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

Cause:

 A statement executed a trigger or custom PL/SQL function. That trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function.

Action:

 The options to resolve this Oracle error are:
  1. Re-write the trigger/function so that it does not try to modify/query the table in question.

For example, if you've created a trigger against the table called orders and then the trigger performed a SELECT against the orders table as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
    ON orders
    FOR EACH ROW

DECLARE
    v_quantity number;

BEGIN

    SELECT quantity
    INTO v_quantity
    FROM orders
    WHERE order_id = 1;

END;


You would receive an error message as follows:


When you create a trigger against a table, you can't modify/query that table until the trigger/function has completed.

Remember that you can always use the :NEW and :OLD values within the trigger, depending on the type of trigger. Learn more about Triggers.