Work around for mutating problem in Oracle Triggers

Trigger with mutating problem

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;
/

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'

 drop trigger trg_check_salary;

Remedy for mutating problem

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

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

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;
/

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;
/

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;
/

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