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 |