ORA-4091: table <schema>.tablename 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.

Solution with Example:

SQL> CREATE TABLE TRIGGER_TEST AS SELECT * FROM USER_OBJECTS;

Table created.

SQL>

CREATE OR REPLACE TRIGGER TEST_TRIGGER

AFTER UPDATE OF STATUS ON TRIGGER_TEST

FOR EACH ROW

DECLARE

v_Count NUMBER;

BEGIN

SELECT count(*) INTO v_count FROM TRIGGER_TEST WHERE status = 'INVALID';

dbms_output.put_line('Total Invalid Objects are ' || v_count);

END;

/

SQL> set serveroutput on

SQL> update TRIGGER_TEST set status = 'INVALID' where object_name = 'TEST1';

update TRIGGER_TEST set status = 'INVALID' where object_name = 'TEST1'

*

ERROR at line 1:

ORA-04091: table SCOTT.TRIGGER_TEST is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.TEST_TRIGGER", line 4

ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIGGER'

You cannot look at or modify the table that is mutating. Starting with 11g you can work around this using compound triggers

SQL>

CREATE OR REPLACE TRIGGER TEST_TRIGGER

FOR UPDATE

ON TRIGGER_TEST

COMPOUND TRIGGER

/* Declaration Section*/

v_count NUMBER;

AFTER EACH ROW IS

BEGIN

dbms_output.put_line('Update is done');

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

SELECT count(*)

INTO v_count

FROM TRIGGER_TEST

WHERE status = 'INVALID';

dbms_output.put_line('Total Invalid Objects are ' || v_count);

END AFTER STATEMENT;

END TEST_TRIGGER;

/

SQL> update TRIGGER_TEST set status = 'INVALID' where object_name = 'TEST1';

SQL> set serveroutput on

SQL> update TRIGGER_TEST set status = 'INVALID' where object_name = 'TEST1';

Update is done

Total Invalid Objects are 7

1 row updated.

Guidelines for Compound Triggers

· Compound triggers combine all triggers timing in one trigger body.

· Compound triggers support only DML operations and it doesn't support DDL and system operation.

· :OLD and :NEW variable identifiers can be used only in ROW level blocks( BEFORE EACH ROW, AFTER EACH ROW )

· PRAGMA_AUTONOMOUS_TRANSACTION not supported

· Compound trigger does not support WHEN clause so you cannot use WHEN clause in compound trigger to improve performance of triggers.

· There is one declaration section for all trigger timing which can share variables until finish transaction.

· Duplicate of trigger timing are permitted in compound trigger.

· INSERTING, UPDATING and DELETING predicates still available in compound trigger