Troubleshoot fix resolve ORA-04091: table scott.EMP is mutating, trigger/function may not see it

advertisements

_____________________________________________________________________________________________________________________

ORA-04091: table scott.EMP is mutating, trigger/function may not see it

Explanation:

04091, 00000, "table %s.%s is mutating, trigger/function may not see it"

// *Cause: A trigger (or a user defined plsql function that is referenced in

//         this statement) attempted to look at (or modify) a table that was

//         in the middle of being modified by the statement which fired it.

// *Action: Rewrite the trigger (or function) so it does not read that table.

Example Scenario

SQL> create table emp (name varchar2(20), age number(2));

Table created.

SQL> insert into emp values ('James', 43);

1 row created.

SQL> insert into emp values ('Toad',52);

1 row created.

SQL> insert into emp values ('John',32);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NAME                        AGE

-------------------- ----------

James                        43

Toad                         52

John                         32

  1  create trigger emp_tr after update on emp for each row

  2  begin

  3  update emp set age=33 where Name='James';

  4* end;

SQL> /

Trigger created.

SQL> update emp set age =87 where name='James';

update emp set age =87 where name='James'

       *

ERROR at line 1:

ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.EMP_TR", line 2

ORA-04088: error during execution of trigger 'SCOTT.EMP_TR'

Explanation:

During the PL/SQL development the developers used to face this error and it kill the valuable time to resolve. Table mutating error fired when a trigger is trying to update or insert the same underlying table which the trigger is created. This error does not show up during the coding face where as it comes the end user tries to manipulate the table data.

For cascade Update and Insert functions, using stored triggers and procedures will result in an ORA-04091 - "Table <table_name> is mutating" error.

ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A proper knowledge and understanding of logic of the triggers will help you avoid that error.

Reason#1. A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

Reason#2. If the trigger is having statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

It must be stressed that this solution should ONLY be used to overcome DML restrictions imposed on triggers in order to maintain referential integrity. Whenever possible it is recommended that normal declarative integrity should be used to maintain foreign key integrity. Enforcing this integrity through stored triggers and procedures will have an effect on performance compared with declarative integrity.

For this solution to work correctly there must be no declarative integrity constraints between objects to enforce the foreign key constraint. The basic principle behind this solution is to suppress the validation checks performed as a result of inserting or updating a foreign key in the CHILD table triggered by a cascade Update or Insert. These checks would normally verify the existence of the new foreign key value in the PARENT record (by SELECTING from the parent table). The suppression of this check is only carried out as a direct result of Cascade Update or Delete, as we can be confident that this new value for the foreign key in the CHILD record does exist (i.e. a result of it being inserted or updated in the PARENT table). In all other circumstances no suppression will take place (e.g. when changing the DEPTNO of an employee or when inserting a new employee).

The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.

To describe this method, the GRADE table will be used. (Table definition and SQL to create the data is provided further down in this Note.)

The table GRADE contains information on salary limits for each grade. The salary limits are also based on a time factor, i.e. the employees' salary is determined by checking which grade level was effective when they joined or reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc grade;
Name         Type
------       ---------------
GRADE        NUMBER
LOSAL        NUMBER
HISAL        NUMBER
START_DATE   DATE
END_DATE     DATE

The table data looks like:

GRADE   LOSAL   HISAL   START_DATE END_DATE
------- ------- ------- ---------- ---------
1       1000    2000    1-APR-94   3-AUG-95
1       1200    2200    3-AUG-95 <---- Null
2       1500    3000    23-JUL-92 12-DEC-93
2       1600    3200    12-DEC-93 11-JAN-95
2       1800    3400    11-JAN-95 <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) not the employees who had review between 1-APR-94 to 3-AUG-95 will be in the range (1000-2000). This is a purely hypothetical scenario.
The objective is to devise a trigger that does the following when a new record is inserted:

·         Integrity checking for overlapping dates, i.e. the new record can't have a start date that is already covered.

·         Update the record for the current grade to make the end_date equal to the start date of the new record (the new record's end_date must be null as that is the current record).

In both cases the table GRADE has to be selected and updated on the after insert row trigger on the same table. But the table will be mutating when the trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_GRADE after insert on GRADE
for each row
declare
   hold_found varchar2(1);
begin
   select 'Y' into hold_found from GRADE where grade = :new.grade
      and end_date is null and start_date > :new.start_date;
EXCEPTION
   when NO_DATA_FOUND then
      raise_application_error(-20000,'Overlapping Dates');
end;
/

Although the trigger can be created with no errors, when a user tries to insert into the table the following mutating table error is returned:

SQL> insert into GRADE values (2, 9000, 100000, '25-dec-95', null);
insert into GRADE values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-04091: table SCOTT.GRADE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TAIUDR_GRADE", line 4
ORA-04088: error during execution of trigger 'SCOTT.TAIUDR_GRADE'

SOLUTION

The following approach is another possibility for the task:

• Create a package "GRADE_PKG" that contains PL/SQL tables for holding the
  GRADE data. Here 3 tables are created - one for holding start_dates, one
  for end_dates, and one for holding the change_flag that identifies the
  updated row.
• Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
  with the start dates, end_dates and changed_grades flag ('N'). Although this
  still executes a SELECT agaist the GRADE table, the mutating table restriction,
  applies to all triggers that use the FOR EACH ROW clause and this new
  trigger will be a STATEMENT or table level trigger.
• Create an AFTER INSERT ROW trigger that compares the newly inserted row
  against this PL/SQL table not the Database table. This way the integrity
  check can be done. The same trigger should assign the new end_date value
  to the PL/SQL table and update the value of the flag to indicate that this
  has to be changed.
• Create a AFTER INSERT STATEMENT trigger to update the GRADE table with
  the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below. CODE:
This is the SQL used to create the test table and populate it with data:

drop table GRADE;

CREATE TABLE GRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER,
START_DATE DATE,
END_DATE DATE);

INSERT INTO GRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO GRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO GRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO GRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
INSERT INTO GRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package GRADE_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_grade chartabtype;
   start_date_tab_size binary_integer;
end;
/

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_GRADE before insert on GRADE
declare
   hold_start_date date;
   hold_end_date date;
   hold_rowid rowid;
   hold_grade binary_integer;
   cursor start_date_cur is select rowid, grade, start_date
      from GRADE where end_date is null order by grade;
begin
   open start_date_cur;
   loop
      fetch start_date_cur into hold_rowid, hold_grade, hold_start_date;
      exit when start_date_cur%notfound;
      GRADE_pkg.start_date_tab(hold_grade) := hold_start_date;
      GRADE_pkg.end_date_tab(hold_grade) := hold_end_date;
      GRADE_pkg.rowid_tab(hold_grade) := hold_rowid;
      GRADE_pkg.changed_grade(hold_grade) := 'N';
   end loop;
   GRADE_pkg.start_date_tab_size := hold_grade;
   close start_date_cur;
end;
/

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_GRADE after insert on GRADE
for each row
begin
   if (:new.grade <= GRADE_pkg.start_date_tab_size) then
      if GRADE_pkg.start_date_tab(:new.grade) > :new.start_date then
         raise_application_error(-20001,'Overlapping Dates');
      end if;
      GRADE_pkg.end_date_tab(:new.grade) := :new.start_date;
      GRADE_pkg.changed_grade(:new.grade) := 'Y';
   else
      GRADE_pkg.start_date_tab(:new.grade) := :new.start_date;
      GRADE_pkg.end_date_tab(:new.grade) := :new.end_date;
      GRADE_pkg.changed_grade(:new.grade) := 'N';
      GRADE_pkg.start_date_tab_size :=
      GRADE_pkg.start_date_tab_size + 1;
   end if;
end;
/

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_GRADE after insert on GRADE
begin
   for i in 1..(GRADE_pkg.start_date_tab_size) loop
      if (GRADE_pkg.changed_grade(i) = 'Y') then
         update GRADE set end_date = GRADE_pkg.end_date_tab(i)
            where rowid = GRADE_pkg.rowid_tab(i);
      end if;
   end loop;
end;
/

Once all the package and triggers are created, the same insert doesn't succeed but gives the user the a more appropriate error:

SQL> insert into GRADE values (2, 9000, 100000, '25-dec-93', null);
insert into GRADE values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-20001: Overlapping Dates
ORA-06512: at "SCOTT.TAIUDR_GRADE", line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_GRADE'

CONCLUSION:
Above method is not submitted as a solution to all cases, it is provided as one possibility. The test and code are provided so modifications can be made to suit the individual cases. Be sure to test this completely before relying on its functionality.

_____________________________________________________________________________________________________________________

Website Stats