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