Mutation Concept in PL/SQL

What is Mutating Table?
     The Mutating Table is defined as the table that is changing or in other words A Mutating Table is a table which is being updated by Insert , update or delete triggers.

    It can also be a table which is being updated when delete cascade is run. A table is not considered mutating for statement level triggers.

What is Mutating Error?
    The error is encountered when a row level trigger accesses the same table on which it is based, while executing.

    The Mutating error is not only encountered during query, but also for insert, update and delete statements present in the trigger. When we try to break the read consistent rule of Oracle, it prevent us to do so by raising the mutating error. Mutating error you will face with Triggers and Functions.

Let us discuss in detail with example

Mutating Error with Triggers.
1) Create a table
CREATE TABLE mutating_err_test (id number,name varchar2(10));

2) Create an AFTER insert trigger for the table
CREATE OR REPLACE TRIGGER mutating_err_test_trig AFTER INSERT OR UPDATE OR DELETE ON mutating_err_test FOR EACH ROW DECLARE l_count number; begin SELECT COUNT(*) INTO l_count FROM mutating_err_test; END;

3) Try to insert a row to the table
INSERT INTO mutating_err_test values(1,'RG');

4) You will get below error

5) Try to delete the data from the table

     This Mutating occurs when a foreign key reference is present with an on-delete-cascade option. A row level trigger on the master table will mutate if the detail table is being referred to in the trigger for a delete transaction.

How to remove mutating error with Triggers:
1) Try to avoid referring the same table or child table which has an on-delete-cascade option in the trigger.
2) Using PRAGMA AUTONOMOUS _TRANSACTION you can remove the Mutating error.

6) Modify the trigger with PRAGMA AUTONOMOUS _TRANSACTION
CREATE OR REPLACE TRIGGER mutating_err_test_trig AFTER INSERT OR UPDATE OR DELETE ON mutating_err_test FOR EACH ROW DECLARE l_count number; PRAGMA AUTONOMOUS_TRANSACTION; begin SELECT COUNT(*) INTO l_count FROM mutating_err_test; END;

7) Try to insert a row into the table
INSERT INTO mutating_err_test values(3,'KG');

The row is successfully inserted into the table

8) Now try to delete the data from the table

The data is successfully deleted from the table.

Mutating Error with Function.
1) You can refer the same table which is created in set 1 of above example.
2) Create a function which returns the count +1 value from the table .
CREATE OR REPLACE FUNCTION cnt_mutating_err_test RETURN NUMBER IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mutating_err_test; RETURN(l_count+1); END;  

3) Try to insert id to the table which is returning from the function
INSERT INTO mutating_err_test(id) select cnt_mutating_err_test() FROM dual;

The Mutating Error will occur.

4) Modify the function with PRAGMA AUTONOMOUS_TRANSACTION
CREATE OR REPLACE FUNCTION cnt_mutating_err_test RETURN NUMBER IS l_count NUMBER; PRAGMA autonomous_transaction; BEGIN SELECT COUNT(*) INTO l_count FROM mutating_err_test; RETURN(l_count+1); END;

5) Now try to insert the values to the table
INSERT INTO mutating_err_test(id) select cnt_mutating_err_test() FROM dual;

The ID has been success fully inserted into the table without Mutating error.

Note:While inserting If you call the function in Values() then it will not through the Mutating Error .
INSERT INTO mutating_err_test values((select cnt_mutating_err_test() from dual) ,'RG');

    
   The row has been inserted successfully without throwing the Mutating Error Because the function we are calling in Values() will execute first and record will be not locked.