A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table.
This restriction prevents a trigger from seeing an inconsistent set of data. This restriction applies to all triggers that use the FOR EACH ROW clause.
Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back,
and control is returned to the user or application.
Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger.
So basically we are trying to select records in the trigger from the table that owns the trigger.
This creates inconsistency and Oracle throws a mutating error.
Let us take a simple scenario in which we have to know total number of invalid objects after any object status is updated to ‘INVALID’.
We will see it with an example. First let us create a table and then trigger.
SET SERVEROUTPUT ON
CREATE TABLE TEST AS SELECT * FROM USER_OBJECTS;
CREATE OR REPLACE TRIGGER UAT_TEST
AFTER UPDATE OF STATUS
ON TEST
FOR EACH ROW
DECLARE
v_Count NUMBER;
BEGIN
SELECT COUNT (*)
INTO v_count
FROM TEST
WHERE status = 'INVALID';
DBMS_OUTPUT.put_line ('Total Invalid Objects are ' || v_count);
END;
Now if we try to change the status of any object to ‘INVALID’, we will run into mutating error as we are trying to update the record
and trigger is trying to select total number of records in ‘INVALID’ status from the same table.
update test
set status = 'INVALID'
where object_name = 'TEST';
ORA-04091: table HR.TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.UAT_TEST", line 4
ORA-04088: error during execution of trigger 'HR.UAT_TEST'
Having said that there are different ways we can handle mutating table errors. Let us start taking one by one scenario.
First one is to create statement level trigger instead of row level.
If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER UAT_TEST
AFTER UPDATE OF STATUS
ON TEST
--FOR EACH ROW
DECLARE
v_Count NUMBER;
BEGIN
SELECT COUNT (*)
INTO v_count
FROM TEST
WHERE status = 'INVALID';
DBMS_OUTPUT.put_line ('Total Invalid Objects are ' || v_count);
END;
update test
set status = 'INVALID'
where object_name = 'TEST';
Total Invalid Objects are 1
1 row updated.
When we defined statement level trigger, update went through fine and it displayed the total number of invalid objects.
Why this is a problem when we are using ‘FOR EACH ROW’ clause?
As per Oracle documentation, the session, which issues a triggering statement on the table, cannot query the same table so that trigger cannot see inconsistent data.
This restriction applies to all the row level triggers and hence we run into mutating table error.
In 11g compound trigger can resolve mutating table error. Let’s create a compound trigger first:
COMPOUND TRIGGER:
CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR UPDATE
ON 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 TEST
WHERE status = 'INVALID';
dbms_output.put_line('Total Invalid Objects are ' || v_count);
END AFTER STATEMENT;
END TEST_TRIG_COMPOUND;
/
Select count(*) from test where status = 'INVALID';
UPDATE TEST
SET status = 'INVALID'
WHERE object_name = 'TEST';
1 row selected.
Update is done
Total Invalid Objects are 0
Total Invalid Objects are 1
1 row updated.
Cheers
Rajani
No comments:
Post a Comment