Mutating Trigger and Solutions Example through statement level trigger, Pragma autonomous transaction and Compound trigger

                Many people who are worked in pl/sql have encountered ORA-04091 table <tablename> is mutating Trigger/function might not see it at some time or the other during the development process.We can resolve this issue using different technologies we can see some of them in this blog.
               
             Mutating error occurs when we are performing some DML(insert,delete,update) operations and we are trying to select the affected records from the same trigger. So basically we are trying to select the records in the trigger from the table that owns the trigger. This creates inconsistency issue and Oracle throws a mutating error.

            Let us take a simple scenario. We are selecting the records count from student_table after updating the result coloumn is 'Pass' through TEST_TRIGGER(This trigger is fired on student_table). 

Step 1: create the table student_table

create table student_table(rollnumber number,name varchar2(50),marks number,section varchar2(3),result varchar2(30))

Step 2: insert 6 records in to the table student_table

 begin
insert into student_table values(1000,'chidambaram',99,'A','Pass');
insert into student_table values(1001,'raja',76,'A','Pass');
insert into student_table values(1002,'ram',48,'A','Fail');
insert into student_table values(1003,'jaya',36,'A','Fail');
insert into student_table values(1004,'bala',99,'A','Pass');
insert into student_table values(1005,'sam',76,'A','Pass');
commit;
end;


SQL> select * from student_table;

ROLLNUMBER NAME               MARKS    SECTION    RESULT
---------- --------------------------------- ----- -------
      1000 chidambaram          99        A       Pass
      1001 raja                 76        A       Pass
      1002 ram                  48        A       Fail
      1003 jaya                 36        A       Fail
      1004 bala                 99        A       Pass
      1005 sam                  76        A       Pass

6 rows selected

Step 3: Create the trigger test_trigger on student_table

CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER UPDATE ON student_table
FOR EACH ROW
DECLARE
ln_count NUMBER;
BEGIN

SELECT count(*)
INTO ln_count
FROM student_table
WHERE result = 'Pass';
dbms_output.put_line('Total Number of Students in Passed in A section is '||ln_count);
END;
/

                    Now if we try to change the status of result coloumn to 'Pass' for the rollnumber 1003, We can see mutating error as we are trying to update the record and the trigger is trying to select total number of records which the result is 'Pass' from the same table.


SQL> update student_table set result='Pass' where rollnumber=1003;

ORA-04091: table APPS.STUDENT_TABLE is mutating, trigger/function may not see it
ORA-06512: at "APPS.TEST_TRIGGER", line 5
ORA-04088: error during execution of trigger 'APPS.TEST_TRIGGER'

Step 4:   Avoid mutating error : Statement level trigger

First one is to create statement level trigger instead of row level trigger. 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 TEST_TRIGGER
AFTER UPDATE ON student_table
DECLARE
ln_count NUMBER;
BEGIN

SELECT count(*)
INTO ln_count
FROM student_table
WHERE result = 'Pass';
dbms_output.put_line('Total Number of Students Passed in A section is '||ln_count);
END;
/


Now let us fire the same update statement again.


SQL> update student_table set result='Pass' where rollnumber=1003;

Total Number of Students Passed in A section is 5

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.

Step 5:   Avoid mutating error : using pragma autonomous transaction

 

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 TEST_TRIGGER
AFTER UPDATE ON student_table
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
ln_count NUMBER;
BEGIN

SELECT count(*)
INTO ln_count
FROM student_table
WHERE result = 'Pass';
dbms_output.put_line('Total Number of Students in Passed in A section is '||ln_count);
END;
/
 
Now let is issue the update statement again and observe the results.

SQL> update student_table set result='Pass' where rollnumber=1003;

Total Number of Students in Passed in A section is 4

1 row updated
If you closely look at the output, you will see only 5 students are passed while statement level trigger fired and 4 students are passed in row level trigger fired . Let us try to update multiple result coloumns at the same time.

SQL> rollback;

Rollback complete

SQL> update student_table set result='Pass' where rollnumber in(1003,1002);

Total Number of Students in Passed in A section is 4
Total Number of Students in Passed in A section is 4

2 rows updated

Step 6:  Avoid mutating error : using compound trigger 

 

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. 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_COMPOUND_TRIGGER
FOR UPDATE
ON student_table
COMPOUND TRIGGER

/* Declaration Section*/
ln_count NUMBER;

--ROW level
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('Update is Completed');
END AFTER EACH ROW;

--Statement level
AFTER STATEMENT IS
BEGIN
SELECT count(*)
INTO ln_count
FROM student_table
WHERE result = 'Pass';
dbms_output.put_line('Total Number of Students Passed in A section is '||ln_count);
END AFTER STATEMENT;

END TEST_COMPOUND_TRIGGER;
/

Now let us check how many records are updated in student_table


SQL> select * from student_table;
ROLLNUMBER NAME               MARKS    SECTION    RESULT
---------- --------------------------------- ----- -------
      1000 chidambaram          99        A       Pass
      1001 raja                 76        A       Pass
      1002 ram                  48        A       Fail
      1003 jaya                 36        A       Fail
      1004 bala                 99        A       Pass
      1005 sam                  76        A       Pass

 6 rows selected


SQL> select count(*) as TOTAL from student_table where result='Pass';

     TOTAL
----------
         4

SQL> update student_table set result='Pass' where rollnumber=1003;

Update is Completed
Total Number of Students Passed in A section is 5

1 row updated


Here we get correct result without getting mutating table error. This is also one very good advantage of compound triggers