Srikanth Technologies

Work around for mutating problem in Oracle Triggers

If you have created a couple of triggers in Oracle then you must have come across a problem called "Table is mutating; trigger may not read or modify it".

Mutating problem occurs if you try to access a table from a trigger which is fired by a command that modified the table. For example, if you are updating EMPLOYEES table and try to access EMPLOYEES table using SELECT statement in before or after row-level trigger then Oracle complains that the table is being modified so it cannot be read from the trigger.

Let us understand the problem with an example. Assume we have to ensure the salary of manager is always more or equal to the salaries of the employees whom he/she manages. In HR account of Oracle, we have EMPLOYEES table containing employee_id, salary and manager_id columns (apart from other not related to this example). We have to ensure whenever we are inserting or updating a row in EMPLOYEES table the salary of the employee is less than or equal to the salary of the manager, if manager_id is not null.

Trigger with mutating problem

Let us try to create a trigger to accomplish the task as follows:

create or replace trigger trg_check_salary 
before update
on employees
for each row 
declare
   v_salary employees.salary%type;
begin
   if  :new.manager_id is not null then 
       /* get the salary of the manager */
       select  salary into v_salary
       from employees
       where employee_id = :new.manager_id;
   
       if  v_salary < :new.salary then
         raise_application_error(20100,'Employee salary is more than manager'); 
       end if;
   end if;
end;
/
Now issue the following command to update EMPLOYEES table as follows and see the message issued by Oracle.


SQL> update employees set salary = 10000 where employee_id = 110;
update employees set salary = 10000 where employee_id = 110
       *
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.TRG_CHECK_SALARY", line 6
ORA-04088: error during execution of trigger 'HR.TRG_CHECK_SALARY'

As we understood, the problem is, we are trying to access the table (EMPLOYEES) that is being modified by the UPDATE command.

Before we apply the remedy, let us drop the above trigger to ensure it is not fired when we update or insert rows into EMPLOYEES table.

 drop trigger trg_check_salary;

Remedy for mutating problem

The remedy for mutating problem requires some process. We need to create a package to store the data being modified and write three triggers. The following process summaries the procedure.
  1. We need to create a package that stores the data in PL/SQL tables for rows that are being modified by the command
  2. Create a BEFORE STATEMENT trigger to initialize variables in the package
  3. Create an AFTER ROW trigger to insert data from the row being modified to PL/SQL tables
  4. Create an AFTER STATEMENT trigger to validate the data that is inserted into PL/SQL tables and raise error if any invalid data is found

    Oracle allows STATEMENT level trigger to access the data from the table as by that time the data is already modified in the table. So it is possible to compare the data that is being modified with the data in the table during this trigger.

Creating a package

The following is the package that stores data from the rows that are being modified.


CREATE OR REPLACE PACKAGE trg_check_pkg IS
   PROCEDURE init_data;
   PROCEDURE insert_data(mgrid IN employees.manager_id%type, sal IN employees.salary%type);
   PROCEDURE check_data;
END;

CREATE OR REPLACE PACKAGE BODY trg_check_pkg IS TYPE mgrid_table IS TABLE OF number(5) index by binary_integer; /* table type */ mgrids mgrid_table; /* table to store manager ids */ TYPE salary_table IS TABLE OF number(5) INDEX BY BINARY_INTEGER; /* table type */ salaries salary_table; /* table to store salaries */ pos binary_integer; /* table index */ PROCEDURE init_data IS BEGIN pos := 0; END; PROCEDURE insert_data(mgrid IN employees.manager_id%type, sal IN employees.salary%type) IS BEGIN pos := pos + 1; salaries(pos) := sal; mgrids (pos) := mgrid; END; PROCEDURE check_data IS dummy number; BEGIN WHILE pos > 0 LOOP BEGIN /* check whether salary of the manager is more than employee's salary */ SELECT 1 INTO dummy FROM employees WHERE salary >= salaries(pos) and mgrids(pos) = employee_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,'Manager salary must be more than or equal to employee salary'); END; pos := pos - 1; END LOOP; END; END;

Create required trigger

Now, in order to store data that is being update into variables of package and check the data in the tables subsequently, create the following triggers.
 

First of three triggers is BEFORE STATEMENT trigger to initialize package data.

CREATE OR REPLACE TRIGGER trg_check_salary_bs
BEFORE insert or update OF salary,manager_id   /* statement-level trigger */
ON employees  
BEGIN
     trg_check_pkg.init_data;
END;
/

Second trigger is AFTER ROW trigger to store data that is being modified by the command.

CREATE OR REPLACE TRIGGER trg_check_salary_ar
After INSERT OR UPDATE OF salary, manager_id
ON employees
FOR EACH ROW  /* row-level trigger */
BEGIN
   IF :new.manager_id IS NOT NULL THEN
       trg_check_pkg.insert_data( :new.manager_id, :new.salary);
   END IF;
END;
/

Third trigger is AFTER STATEMENT trigger that is used to check whether the data that was modified is valid.

CREATE OR REPLACE TRIGGER  trg_check_salary_as
After INSERT OR UPDATE OF salary,manager_id  /* statement-level trigger */
ON employees
BEGIN
     trg_check_pkg.check_data;
END;
/
Now try updating the salary of an employee(110) to  13000, which is more than the salary of the manager (12000). You get the following output.

SQL> update employees set salary = 13000 where employee_id = 110;
update employees set salary = 13000 where employee_id = 110
         *
ERROR at line 1:
ORA-20000: Manager salary must be more than employee salary
ORA-06512: at "HR.TRG_CHECK_PKG", line 32
ORA-06512: at "HR.TRG_CHECK_SALARY_AS", line 2
ORA-04088: error during execution of trigger 'HR.TRG_CHECK_SALARY_AS'


Post Your Comment

Image

Enter the code given in the above image :
Enter Your Name :
Enter Your Email Address :
Enter Your Comment :

Comments


Posted By preethi On 17-Jun-10 07:50:11 AM

thanks for providing solution for mutating problem,sir..It is very helpful



Posted By ravikumar On 05-Aug-11 01:31:17 AM

gud one



Posted By Raaj On 26-Oct-12 09:05:07 AM

Article is nice.



Posted By JW On 14-May-13 04:01:34 AM

Very clever, But writing 3 triggers to get around a design flaw is not the answer. Create a stored procedure that checks the data before the update.




Posted By SG On 01-Feb-14 03:21:36 PM

think, intead of these three triggers we can get rid off this using a compound trigger or at least using a statement level trigger



Posted By Shubham On 12-Feb-14 04:29:03 PM

Good explanation sir



Posted By Shubham On 12-Feb-14 04:35:55 PM

How can we define the execution order of these triggers?



Posted By Manjunath On 18-Aug-15 04:00:14 PM

Cau you please tell me how to avoid mutating trigger error while we update triggering table.



Posted By Girish On 03-Mar-16 01:38:23 PM

Pretty smart idea. Kudos to the author!

JW, Stored proc has to be explicitly called every time to check it. Whereas trigger automatically fires to ensure data integrity.

Shubham, From Oracle 11g onwards, you can use FOLLOWS trigger_name to make sure the order of triggers.

Manjunath, Answer to your query is this article itself!