Trigger with mutating problem
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.
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 or equal to 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'
I hope you all have enjoyed reading this article. Comments are welcome....
