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