Mutating table errors using triggers and resolution in Oracle

As mutating means changing, a mutation table is defined as a table that is changing. you can encountered the mutating table error ORA-04091 generally with triggers, When a trigger tries to query or modify the same table that caused the trigger to fire, creating a conflict between the old and new states of the table. It majorly happen with row-level triggers.

There are several ways to fixed or avoid the mutating table error:

1. Avoid the use of trigger in your database.
2. Use statement based trigger instead of row-level trigger.
3. Using autonomous transaction.
4. Using compound trigger.

Avoid the use of trigger in your database.

It means you can avoid the use of triggers if you are using triggers for something audit purpose, you can choose audit option for the tables and find other way to handle if possible.

Use statement based trigger instead of row-level trigger

Take an example to show the mutating error by creating one table and trigger on that table with row level.

-- Create table
Create table employee (id number, name varchar(100), status varchar(10));

-- Insert data into table
Insert into employee values(1,'RAM' ,'ACTIVE');
insert into employee values(2,'SHAM','ACTIVE');
insert into employee values(3,'SHWETA','INACTIVE');
insert into employee values(4,'RAKESH','ACTIVE');

-- Create trigger for each row
CREATE TRIGGER tn 
AFTER UPDATE OF status ON employee
FOR EACH ROW
DECLARE
v_count number;
begin
select count(*) into v_count from employee where status = 'ACTIVE';
end;
/


-- update the employee table
update employee set status = 'ACTIVE' where id in (3);

SQL> update employee set status = 'ACTIVE' where id in (3);
update employee set status = 'ACTIVE' where id in (3)
       *
ERROR at line 1:
ORA-04091: table C##TEST.EMPLOYEE is mutating, trigger/function may not see it
ORA-06512: at "C##TEST.TN", line 4
ORA-04088: error during execution of trigger 'C##TEST.TN'

To Resolved the error, we need to change the trigger to Statement level.

--  Create statement trigger
CREATE TRIGGER tn 
AFTER UPDATE OF status ON employee
FOR EACH ROW
DECLARE
v_count number;
begin
select count(*) into v_count from employee where status = 'ACTIVE';
end;
/

--  fire update statement
SQL> update employee set status = 'ACTIVE' where id in (3);
1 row updated.

Use autonomous transaction

Autonomous transaction make the transaction independent. Oracle Autonomous Transactions can take place independently of any transaction that calls it

-- drop the previous trigger and create this one:
CREATE TRIGGER tn 
AFTER UPDATE OF status ON employee
FOR EACH ROW
DECLARE
v_count number;
PRAGMA autonomous_transaction;
begin
select count(*) into v_count from employee where status = 'ACTIVE';
end;
/

-- Again run the update statement no error reported.
SQL> update employee set status = 'ACTIVE' where id in (3);
1 row updated.

Using compound trigger

The Compound trigger is a trigger that allows you to specify actions for each of four timing points in the single trigger body. In this case its like similar we use statement trigger for that particular statement.

-- Drop previous one and create a new COMPOUND TRIGGER 
CREATE TRIGGER tn 
FOR UPDATE ON employee
compound trigger

v_count number;
AFTER STATEMENT IS
begin
select count(*) into v_count from employee where status = 'ACTIVE';
end after statement;
end tn;
/

-- Again run the update statement no error reported.
SQL> update employee set status = 'ACTIVE' where id in (3);
1 row updated.