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.

We need to create a package that stores the data in PL/SQL tables for rows that are being modified by the command
Create a BEFORE STATEMENT trigger to initialize variables in the package
Create an AFTER ROW trigger to insert data from the row being modified to PL/SQL tables
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'