Workaround for The Mutating Table Problem Caused by Triggers

One of the situations when we face with the mutating table error is when we have a trigger that tries to access any row in the table that it triggers from.

Suppose that we have the following table:

SQL> desc tbl_salary; 

 Name                            Null?    Type 
 ------------------------------- -------- ---- 
 RATE                                    NUMBER 
 LOW                                      NUMBER 
 HIGH                                     NUMBER 
 START_DATE                               DATE 
 END_DATE                                 DATE 
 
SQL> select * from tbl_salary; 

RATE    LOW     HIGH    START_DATE       END_DATE 
------- ------- ------- ----------       --------- 
1       2000    3000    1-APR-01         3-AUG-02 
1       2200    3200    3-AUG-02                   
 
And we want to create a trigger that does the following when we insert a new record: 
 
Check that the new record does not have a start date that is already covered.

Update the record for the current RATE to make the END_DATE equal to the START_DATE of the new record (the new record's END_DATE should equals null).
 

 
In both cases trigger has to make select and update on the tbl_salary table.

Let's create the following trigger: 
 
create or replace trigger tr_insa_tbl_salary 
after insert on tbl_salary 
for each  row 
declare 
        hold_found      varchar2(1); 
begin 
        select 'Y' into hold_found 
        from tbl_salary 
        where RATE = :new.RATE 
        and end_date is null 
        and start_date > :new.start_date; 
 
exception 
        when NO_DATA_FOUND then 
                raise_application_error(-20000,'Overlapping Dates'); 
end; 
/ 

We created this trigger without any errors, but when we try to insert a new record we receive the mutating table error: 
 
SQL> insert into tbl_salary values (2, 9000, 100000, '25-DEC-02', null); 
insert into tbl_salary values (2, 9000, 100000, '25-dec-93', null) 
            * 
ERROR at line 1: 
ORA-04091: table ALEX.TBL_SALARY is mutating, trigger/function may not see it 
ORA-06512: at line 4 
ORA-04088: error during execution of trigger 'ALEX.TR_INS_TBL_SALARY' 

The possible workaround for this problem is:

Create the tbl_salary_pkg package that contains PL/SQL tables for holding the TBL_SALARY data. We create three PL/SQL tables - one for holding START_DATE, one for END_DATE, and one for holding the CHANGED_RATE flag that identifies the updated row.
Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the START_DATE, END_DATE and CHANGED_RATE flag ('N').
Create the AFTER INSERT ROW trigger that compares a new row with data from tbl_start_date and tbl_end_date tables, assign a new END_DATE value to the tbl_end_date table and update the tbl_changed_rate table.
Create the AFTER INSERT STATEMENT trigger to update the tbl_salary table.

 
drop table tbl_salary; 
 
CREATE TABLE TBL_SALARY 
      (RATE NUMBER, 
        LOW NUMBER, 
        HIGH NUMBER, 
        START_DATE DATE, 
        END_DATE DATE); 
 
INSERT INTO TBL_SALARY VALUES (1,2000,3000, '1-apr-01', '3-aug-02'); 
INSERT INTO TBL_SALARY VALUES (1,2200,3200, '3-aug-02', null); 
INSERT INTO TBL_SALARY VALUES (2,2500,4000, '23-Jul-01', '12-dec-02'); 
INSERT INTO TBL_SALARY VALUES (2,2600,4200, '12-dec-02', '11-jan-03'); 
INSERT INTO TBL_SALARY VALUES (2,2800,4400, '11-jan-03', null); 

create or replace package tbl_salary_pkg as 
type datetabtype        is table of date index by binary_integer; 
type chartabtype        is table of char(1) index by binary_integer; 
type rowidtabtype       is table of rowid  index by binary_integer; 
start_date_tab          datetabtype; 
end_date_tab            datetabtype; 
rowid_tab               rowidtabtype; 
changed_rate            chartabtype; 
start_date_tab_size     binary_integer; 
end; 
/ 

create or replace trigger tr_insb_tbl_salary 
before insert on tbl_salary 
declare 
 hold_start_date  date; 
 hold_end_date  date; 
 hold_rowid   rowid; 
 hold_RATE   binary_integer; 
 cursor start_date_cur is 
  select rowid, rate, start_date 
  from tbl_salary 
  where end_date is null 
  order by rate; 
begin 
 open start_date_cur; 
 loop 
  fetch start_date_cur into 
   hold_rowid, hold_rate, hold_start_date; 
  exit when start_date_cur%notfound; 
  tbl_salary_pkg.start_date_tab(hold_rate) := hold_start_date; 
  tbl_salary_pkg.end_date_tab(hold_rate) := hold_end_date; 
  tbl_salary_pkg.rowid_tab(hold_rate) := hold_rowid; 
  tbl_salary_pkg.changed_rate(hold_rate) := 'N'; 
 end loop; 
 tbl_salary_pkg.start_date_tab_size := hold_rate; 
 close start_date_cur; 
end; 
/ 

create or replace trigger tr_insar_tbl_salary 
after insert on tbl_salary 
for each row 
begin 
 if (:new.rate  :new.start_date then 
   raise_application_error(-20001,'Overlapping Dates'); 
  end if; 
  tbl_salary_pkg.end_date_tab(:new.rate) := :new.start_date; 
  tbl_salary_pkg.changed_rate(:new.rate) := 'Y'; 
 else 
  tbl_salary_pkg.start_date_tab(:new.rate) := :new.start_date; 
  tbl_salary_pkg.end_date_tab(:new.rate) := :new.end_date; 
  tbl_salary_pkg.changed_rate(:new.rate) := 'N'; 
  tbl_salary_pkg.start_date_tab_size := 
  tbl_salary_pkg.start_date_tab_size + 1; 
 end if; 
end; 
/ 

create or replace trigger tr_insa_tbl_salary 
after insert on tbl_salary 
begin 
        for i in 1..(tbl_salary_pkg.start_date_tab_size) loop 
                if (tbl_salary_pkg.changed_rate(i) = 'Y') then 
                        update tbl_salary 
                        set end_date = tbl_salary_pkg.end_date_tab(i) 
                        where rowid = tbl_salary_pkg.rowid_tab(i); 
                end if; 
        end loop; 
end; 
/ 
This method is one way to solve the problem of mutating tables.
First appeared at DBAOnCall.net.
DBAonCall.net is an online journal that covers the fundamental and

essential tasks of Oracle administration. Although it includes information

designed for people who are new to Oracle world, some materials extend

beyond the basis. The primary goal is to make database administration

straightforward - thats why we built DBAonCall.net from a task-oriented

perspective. It covers all aspects of Oracle administration: general

concepts, internal structure, and guiding assumptions, as well as the

commands, procedures, strategies and policies essential to success as a

DBA.


	
Related