Mutating table/trigger error and how to resolve it

A good article on Mutating Table/Trigger and how to avoid this using Compound Triggers. Click Here or continue reading below.

Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other during the development process.  In this blog post, we will cover why this error occurs and how we can resolve it using different methodology.

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.

SQL> CREATE TABLE TEST
2  AS SELECT * FROM USER_OBJECTS;

Table created.

CREATE OR REPLACE TRIGGER TUA_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.

SQL> update test
2  set status = 'INVALID'
3  where object_name = 'TEST1';
update test
*
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
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.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
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 let us fire the same update statement again.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 6

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.

Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
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 let is issue the update statement again and observe the results.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 5

1 row updated.

If you closely look at the output, you will see only 5 objects shown in invalid status while statement level trigger showed 6 objects in invalid status. Let us try to update multiple objects at the same time.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name IN ('T1','T2');
Total Invalid Objects are 6
Total Invalid Objects are 6

2 rows updated.

By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.

In version 11g, Oracle made it much easier with introduction of compound triggers. We have covered compound triggers in a previous blog post. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:

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;
/

Now let us check how many objects are invalid in the test table.

SQL> select count(*) from test where status = 'INVALID';
COUNT(*)
———-
6

Here is the update statement followed by an output.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'T2';
Update is done
Total Invalid Objects are 7

1 row updated.

Here we get correct result without getting mutating table error. This is also one very good advantage of compound triggers. There are other ways also to resolve mutating table error using temporary tables but we have discussed common ones in this blog post.