MAD012999
asked on
Mutating Table Trigger Problem
Mutating Table Trigger Problem
I have 4 tables as follows (I have simplified the tables for this example, and changing the general structure is not an option):
Create Table User_Type
--A record for each type that the user is a member of.
(
User_ID Integer,
User_Type Integer,
Primary Key (User_ID, User_Type)
);
Create Table User_Preference
--A record for each Preference that the user has available.
(
User_ID Integer,
User_Pref Integer,
Primary Key (User_ID, User_Pref)
);
Create Table Type_Preference
--A record for each Preference that the user is granted through their type.
(
User_Type Integer,
User_Pref Integer,
Primary Key (User_Type, User_Pref)
);
Create Table User_Choice
--A record for each Preference that the user selected, but is NOT granted through their type.
(
User_ID Integer,
User_Pref Integer,
Primary Key (User_ID, User_Pref)
);
BASIC Problem:
When a record is inserted into the User_Type table, a Trigger fires that inserts a record into the User_Preference table. That table also has an insert trigger that determines if the record was inserted via a User_Type, and if not, adds a record to the User_Choice table. The 2 triggers follow:
Create or replace trigger trig_User_Type_Insert
after Insert
on User_Type
for each row
begin
insert into User_Preference (User_ID, User_Pref)
select :new.User_ID, tp.user_Pref
from Type_Preference tp
where tp.User_Type = :new.User_Type
and not exists(
select *
from User_Preference up
where up.User_ID = :new.User_ID
and up.User_Pref = tp.User_Pref
);
end;
/
Create or replace trigger trig_User_Preference_Inser
after Insert
on User_Preference
for each row
begin
insert into User_Choice (User_ID, User_Pref)
select :new.User_ID, :new.User_Pref
from dual
where not exists(
select *
from User_Choice uc
where uc.User_ID = :new.User_ID
and uc.User_Pref = :new.User_Pref
)
and not exists(
select *
from User_Type ut, Type_Preference tp
where ut.User_ID = :new.User_ID
and ut.User_Type = tp.User_Type
and tp.User_Pref = :new.User_Pref
);
end;
/
--Load the tables:
insert into Type_Preference (User_Type, User_Pref) values (1, 100);
insert into Type_Preference (User_Type, User_Pref) values (1, 101);
insert into User_Preference (User_ID, User_Pref) values (1001, 100);
insert into User_Preference (User_ID, User_Pref) values (1001, 200);
select * from user_Preference;
-- USER_ID USER_PREF
------------ ----------
-- 1001 100
-- 1001 200
select * from user_Choice;
-- USER_ID USER_PREF
------------ ----------
-- 1001 100
-- 1001 200
Up to here, everything works as expected, but if I add a user type, the following error happens: As records can be inserted into User_Preference either directly or via adding a new User_Type, I am unsure how to get around this.
insert into User_Type (User_ID, User_Type) values (1001, 1);
ERROR at line 1:
ORA-04091: table APP_LSPACE.USER_TYPE is mutating, trigger/function may not see
it
ORA-06512: at "APP_LSPACE.TRIG_USER_PREF
ORA-04088: error during execution of trigger
'APP_LSPACE.TRIG_USER_PREF
ORA-06512: at "APP_LSPACE.TRIG_USER_TYPE
ORA-04088: error during execution of trigger 'APP_LSPACE.TRIG_USER_TYPE
Is there a work-around for this that I am missing?
Thanks in advance.
I have 4 tables as follows (I have simplified the tables for this example, and changing the general structure is not an option):
Create Table User_Type
--A record for each type that the user is a member of.
(
User_ID Integer,
User_Type Integer,
Primary Key (User_ID, User_Type)
);
Create Table User_Preference
--A record for each Preference that the user has available.
(
User_ID Integer,
User_Pref Integer,
Primary Key (User_ID, User_Pref)
);
Create Table Type_Preference
--A record for each Preference that the user is granted through their type.
(
User_Type Integer,
User_Pref Integer,
Primary Key (User_Type, User_Pref)
);
Create Table User_Choice
--A record for each Preference that the user selected, but is NOT granted through their type.
(
User_ID Integer,
User_Pref Integer,
Primary Key (User_ID, User_Pref)
);
BASIC Problem:
When a record is inserted into the User_Type table, a Trigger fires that inserts a record into the User_Preference table. That table also has an insert trigger that determines if the record was inserted via a User_Type, and if not, adds a record to the User_Choice table. The 2 triggers follow:
Create or replace trigger trig_User_Type_Insert
after Insert
on User_Type
for each row
begin
insert into User_Preference (User_ID, User_Pref)
select :new.User_ID, tp.user_Pref
from Type_Preference tp
where tp.User_Type = :new.User_Type
and not exists(
select *
from User_Preference up
where up.User_ID = :new.User_ID
and up.User_Pref = tp.User_Pref
);
end;
/
Create or replace trigger trig_User_Preference_Inser
after Insert
on User_Preference
for each row
begin
insert into User_Choice (User_ID, User_Pref)
select :new.User_ID, :new.User_Pref
from dual
where not exists(
select *
from User_Choice uc
where uc.User_ID = :new.User_ID
and uc.User_Pref = :new.User_Pref
)
and not exists(
select *
from User_Type ut, Type_Preference tp
where ut.User_ID = :new.User_ID
and ut.User_Type = tp.User_Type
and tp.User_Pref = :new.User_Pref
);
end;
/
--Load the tables:
insert into Type_Preference (User_Type, User_Pref) values (1, 100);
insert into Type_Preference (User_Type, User_Pref) values (1, 101);
insert into User_Preference (User_ID, User_Pref) values (1001, 100);
insert into User_Preference (User_ID, User_Pref) values (1001, 200);
select * from user_Preference;
-- USER_ID USER_PREF
------------ ----------
-- 1001 100
-- 1001 200
select * from user_Choice;
-- USER_ID USER_PREF
------------ ----------
-- 1001 100
-- 1001 200
Up to here, everything works as expected, but if I add a user type, the following error happens: As records can be inserted into User_Preference either directly or via adding a new User_Type, I am unsure how to get around this.
insert into User_Type (User_ID, User_Type) values (1001, 1);
ERROR at line 1:
ORA-04091: table APP_LSPACE.USER_TYPE is mutating, trigger/function may not see
it
ORA-06512: at "APP_LSPACE.TRIG_USER_PREF
ORA-04088: error during execution of trigger
'APP_LSPACE.TRIG_USER_PREF
ORA-06512: at "APP_LSPACE.TRIG_USER_TYPE
ORA-04088: error during execution of trigger 'APP_LSPACE.TRIG_USER_TYPE
Is there a work-around for this that I am missing?
Thanks in advance.
ASKER
Hi Mark!
Mike D. here (from WH). I hope all is well with you. I have been out of practice with Oracle for a while now and had forgotten about this problem (SQL Server After insert triggers must be after commit because they work fine.)
Thanks for the info. It will take me some time to figure it out, but I'm sure it will set my direction. For 500 points, I was hoping for some sample code. I'm not exactly sure how to do the things you suggest.
Take care!
Mike
Mike D. here (from WH). I hope all is well with you. I have been out of practice with Oracle for a while now and had forgotten about this problem (SQL Server After insert triggers must be after commit because they work fine.)
Thanks for the info. It will take me some time to figure it out, but I'm sure it will set my direction. For 500 points, I was hoping for some sample code. I'm not exactly sure how to do the things you suggest.
Take care!
Mike
Mark,
Basically you need the following:
1. a package
2. a BEFORE STATEMENT level trigger (for initialising)
3. a BEFORE ROW level trigger
3. an AFTER STATEMENT level trigger
Here is an example:
create or replace package testPack as
w_new_a test1.a%type;
w_new_b test1.b%type;
testex exception;
end testPack;
/
-- BEFORE STMT LEVEL TRIGGER
create or replace trigger BEF_stmt_level_trig
after insert or update on test1
begin
testPack.w_new_a := null;
testPack.w_new_b := null;
end BEF_stmt_level_trig;
/
-- BEFORE ROW LEVEL TRIGGER
create or replace trigger row_level_trig
before insert or update on test1
for each row
begin
testPack.w_new_a := :new.a;
testPack.w_new_b := :new.b;
end row_level_trig;
/
-- AFTER STMT LEVEL TRIGGER
create or replace trigger AFT_stmt_level_trig
after insert or update on test1
declare
w_dummy pls_integer;
begin
select count(*)
into w_dummy
from test1
where a = testPack.w_new_a
and b = testPack.w_new_b;
dbms_output.put_line('Coun
if w_dummy > 1
then
dbms_output.put_line('More
--raise testPack.testex;
end if;
end AFT_stmt_level_trig;
/
sora
Basically you need the following:
1. a package
2. a BEFORE STATEMENT level trigger (for initialising)
3. a BEFORE ROW level trigger
3. an AFTER STATEMENT level trigger
Here is an example:
create or replace package testPack as
w_new_a test1.a%type;
w_new_b test1.b%type;
testex exception;
end testPack;
/
-- BEFORE STMT LEVEL TRIGGER
create or replace trigger BEF_stmt_level_trig
after insert or update on test1
begin
testPack.w_new_a := null;
testPack.w_new_b := null;
end BEF_stmt_level_trig;
/
-- BEFORE ROW LEVEL TRIGGER
create or replace trigger row_level_trig
before insert or update on test1
for each row
begin
testPack.w_new_a := :new.a;
testPack.w_new_b := :new.b;
end row_level_trig;
/
-- AFTER STMT LEVEL TRIGGER
create or replace trigger AFT_stmt_level_trig
after insert or update on test1
declare
w_dummy pls_integer;
begin
select count(*)
into w_dummy
from test1
where a = testPack.w_new_a
and b = testPack.w_new_b;
dbms_output.put_line('Coun
if w_dummy > 1
then
dbms_output.put_line('More
--raise testPack.testex;
end if;
end AFT_stmt_level_trig;
/
sora
It is a pretty common problem in Oracle that happens and frustrates many
application developers and DBAs as the application developers bug them to find
a solution to it. It happens when a trigger on a table tries to insert,
update, or even select the table of whose trigger is being executed. Sometimes
the inability to see the table causes standstill in the development. This
paper and the associated scripts attempt to present a work around for the
problem. The work around is not the best one nor the author claims it to be
an elegant one, but it provides a solution that will be applicable in most
cases. The approach is described by a case study.
SCENARIO
--------
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, I will be using the table SALGRADE.
The table SALGRADE 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 salgrade;
Name Null? 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) nut 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. Our objective is to
devise a trigger that does the following when a new record is inserted:
(1) Integrity checking for overlapping dates, i.e. the new record can't have a
start date that is already covered.
(2) 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 SALGRADE 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_salgrade
after insert on salgrade
for each row
declare
hold_found varchar2(1);
begin
select 'Y' into hold_found
from salgrade
where grade = :new.grade
and end_date is null
and start_date > :new.start_date;
exception
when NO_DATA_FOUND then
raise_application_error(-2
end;
/
Although the trigger can be created with no errors, when a user tries to
insert into the table he will receive the mutating table error:
SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
ORA-06512: at line 4
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
SOLUTION
--------
The following approach is another possibility for the task:
1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
SALGRADE data. Here we create 3 tables - one for holding start_dates, one
for end_dates, and one for holding the change_flag that identifies the
updated row.
2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
with the start dates, end_dates and changed_grades flag ('N').
3. 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.
4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE 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. I urge you to
test them and make any enhancements to them as you find necessary.
CODE:
----
Code to create test table and populate it with data:
drop table salgrade;
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER,
START_DATE DATE,
END_DATE DATE);
INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);
Code for package STEP 1 above:
create or replace package salgrade_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_salgrade
before insert on salgrade
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 salgrade
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;
salgrade_pkg.start_date_ta
salgrade_pkg.end_date_tab(
salgrade_pkg.rowid_tab(hol
salgrade_pkg.changed_grade
end loop;
salgrade_pkg.start_date_ta
close start_date_cur;
end;
/
Code for after insert row trigger STEP 3 above:
create or replace trigger taiudr_salgrade
after insert on salgrade
for each row
begin
if (:new.grade <= salgrade_pkg.start_date_ta
if salgrade_pkg.start_date_ta
> :new.start_date then
raise_application_error(-2
end if;
salgrade_pkg.end_date_tab(
salgrade_pkg.changed_grade
else
salgrade_pkg.start_date_ta
salgrade_pkg.end_date_tab(
salgrade_pkg.changed_grade
salgrade_pkg.start_date_ta
salgrade_pkg.start_date_ta
end if;
end;
/
Code for after insert statement trigger STEP 4 above:
create or replace trigger taiuds_salgrade
after insert on salgrade
begin
for i in 1..(salgrade_pkg.start_dat
if (salgrade_pkg.changed_grad
update salgrade
set end_date = salgrade_pkg.end_date_tab(
where rowid = salgrade_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 salgrade values (2, 9000, 100000, '25-dec-93', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-20001: Overlapping Dates
ORA-06512: at line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
CONCLUSION:
----------
This method is not submitted as a solution to all cases, however, 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 it's functionality.
application developers and DBAs as the application developers bug them to find
a solution to it. It happens when a trigger on a table tries to insert,
update, or even select the table of whose trigger is being executed. Sometimes
the inability to see the table causes standstill in the development. This
paper and the associated scripts attempt to present a work around for the
problem. The work around is not the best one nor the author claims it to be
an elegant one, but it provides a solution that will be applicable in most
cases. The approach is described by a case study.
SCENARIO
--------
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, I will be using the table SALGRADE.
The table SALGRADE 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 salgrade;
Name Null? 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) nut 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. Our objective is to
devise a trigger that does the following when a new record is inserted:
(1) Integrity checking for overlapping dates, i.e. the new record can't have a
start date that is already covered.
(2) 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 SALGRADE 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_salgrade
after insert on salgrade
for each row
declare
hold_found varchar2(1);
begin
select 'Y' into hold_found
from salgrade
where grade = :new.grade
and end_date is null
and start_date > :new.start_date;
exception
when NO_DATA_FOUND then
raise_application_error(-2
end;
/
Although the trigger can be created with no errors, when a user tries to
insert into the table he will receive the mutating table error:
SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
ORA-06512: at line 4
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
SOLUTION
--------
The following approach is another possibility for the task:
1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
SALGRADE data. Here we create 3 tables - one for holding start_dates, one
for end_dates, and one for holding the change_flag that identifies the
updated row.
2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
with the start dates, end_dates and changed_grades flag ('N').
3. 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.
4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE 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. I urge you to
test them and make any enhancements to them as you find necessary.
CODE:
----
Code to create test table and populate it with data:
drop table salgrade;
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER,
START_DATE DATE,
END_DATE DATE);
INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);
Code for package STEP 1 above:
create or replace package salgrade_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_salgrade
before insert on salgrade
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 salgrade
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;
salgrade_pkg.start_date_ta
salgrade_pkg.end_date_tab(
salgrade_pkg.rowid_tab(hol
salgrade_pkg.changed_grade
end loop;
salgrade_pkg.start_date_ta
close start_date_cur;
end;
/
Code for after insert row trigger STEP 3 above:
create or replace trigger taiudr_salgrade
after insert on salgrade
for each row
begin
if (:new.grade <= salgrade_pkg.start_date_ta
if salgrade_pkg.start_date_ta
> :new.start_date then
raise_application_error(-2
end if;
salgrade_pkg.end_date_tab(
salgrade_pkg.changed_grade
else
salgrade_pkg.start_date_ta
salgrade_pkg.end_date_tab(
salgrade_pkg.changed_grade
salgrade_pkg.start_date_ta
salgrade_pkg.start_date_ta
end if;
end;
/
Code for after insert statement trigger STEP 4 above:
create or replace trigger taiuds_salgrade
after insert on salgrade
begin
for i in 1..(salgrade_pkg.start_dat
if (salgrade_pkg.changed_grad
update salgrade
set end_date = salgrade_pkg.end_date_tab(
where rowid = salgrade_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 salgrade values (2, 9000, 100000, '25-dec-93', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
*
ERROR at line 1:
ORA-20001: Overlapping Dates
ORA-06512: at line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
CONCLUSION:
----------
This method is not submitted as a solution to all cases, however, 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 it's functionality.
Hmmmmmm.
Schwertner, I thought we should review previous posts to try and avoid posting duplicate or 'near' duplicate posts, instead, expanding on previous comments - unless of course they are on a different thread altogether
sora
Schwertner, I thought we should review previous posts to try and avoid posting duplicate or 'near' duplicate posts, instead, expanding on previous comments - unless of course they are on a different thread altogether
sora
The purpose is to illustrate to those customers who require one
of the following functional capabilities whilst being able to maintain
referential integrity among objects:
o Cascade Update
o Cascade Delete
o Cascade Insert
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: "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.
ORA-04091 is a very common error that occurs with triggers if triggers are not
managed properly. A full understanding of triggers will help you avoid that
error.
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.
Another way this error can occur is if the trigger has 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 following code illustrates how this is achieved for the cascade Update
scenario, the code can easily be modified to add the other functionality
and encapsulate it all within the same package. The EMP and DEPT table have
been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO
in the DEPT table.
/*************************
CREATE TABLE DEPT
(DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(30),
LOC VARCHAR2(30))
/
CREATE TABLE EMP
(EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(30) NOT NULL,
SAL NUMBER,
JOB VARCHAR2(30),
DEPTNO NUMBER))
/
**************************
/*
The mutation_prevention package is the KEY to the whole solution, it contains
only one variable which will indicate whether integrity checks should be
carried out as a result of an Insert or Update on the foreign key in the EMP
table.
*/
CREATE or replace PACKAGE mutation_prevention AS
fire_trigger varchar2(5) := 'YES';
END mutation_prevention;
/
/*
The package manual_cascade is a general purpose package which can also handle
Insert and Delete functionality
*/
CREATE or replace PACKAGE manual_cascade AS
PROCEDURE cascade_update(old_key IN NUMBER,
new_key IN NUMBER,
result OUT BOOLEAN);
END manual_cascade;
/
/*
The package body contains the procedures which will handle all the cascade
functionality, each will accept 3 parameters :
1. old_key -- The Old value which is used to reference CHILD records.
2. new_key -- The New value which is being inserted or substituted.
3. result -- Boolean returning the result of the operation.
Procedures contained in this package will be called by the trigger on the
parent table [call_manual_cascade].
*/
CREATE or replace PACKAGE BODY manual_cascade AS
PROCEDURE cascade_update(old_key IN NUMBER,
new_key IN NUMBER,
result OUT BOOLEAN) IS
loop_count NUMBER;
dummy_2 NUMBER;
dummy_3 NUMBER;
l_result BOOLEAN := false;
l_old_key NUMBER;
l_new_key NUMBER;
--
-- Declare cursor c1 to lock all child records which may be related to the
-- PARENT record. The NOWAIT is specified such that the procedure does NOT
-- continue to wait if these records are already locked by another user.
--
CURSOR c1 (l_dept NUMBER) IS
SELECT empno, deptno FROM EMP
WHERE deptno = l_dept
FOR UPDATE OF DEPTNO NOWAIT;
--
-- Declare exceptions to gracefully handle the CURSOR irregularities.
--
RESOURCE_BUSY ExCEPTION;
INVALID_CURSOR ExCEPTION;
PRAGMA EXCEPTION_INIT(RESOURCE_BU
PRAGMA EXCEPTION_INIT(INVALID_CUR
BEGIN
--
-- Assign input variables to local variables.
--
l_old_key := old_key;
l_new_key := new_key;
--
-- Check to see if there are any related child records to be updated. If NOT
-- then exit the IF TEST, otherwise proceed in locking the rows.
--
SELECT count(*) INTO loop_count
FROM EMP
WHERE deptno = l_old_key;
IF loop_count >= 1 THEN
--
-- At this stage you could define an REPEAT LOOP which will retry this
-- transaction in case of failure to lock all CHILD records successfully
-- [n] time. This concept is detailed in the PL/SQL manual V2.0 page 5-23
-- in the Error Handling section.
--
BEGIN
--
-- Open the cursor and handle any Error Conditions.
--
OPEN c1 (l_old_key);
IF NOT (c1%ISOPEN) THEN
RAISE INVALID_CURSOR;
END IF;
--
-- If successful then loop and update each row, one at a time until no more
-- rows. Handle Exceptions and Close the cursor.
--
FOR i IN 1..loop_count LOOP
FETCH c1 INTO dummy_2, dummy_3;
UPDATE emp SET deptno = l_new_key WHERE CURRENT OF c1;
END LOOP;
EXCEPTION
WHEN INVALID_CURSOR THEN
result := true;
WHEN RESOURCE_BUSY THEN
result := true;
WHEN OTHERS THEN
raise_application_error(-2
CLOSE c1; ----- ?
END;
END IF;
END cascade_update;
END manual_cascade;
/
/*
This trigger is on the PARENT table DEPT and controls the value of the global
variable [mutation_prevention.fire_
duration of the user session.
*/
CREATE or replace TRIGGER call_manual_cascade
AFTER UPDATE OF deptno ON DEPT
FOR EACH ROW
DECLARE
l_result BOOLEAN;
transaction_failed EXCEPTION;
debug_var varchar2(5);
BEGIN
--
-- Set the global control variable indicating to the [TRIGGER EMP_DEPT_CHECK]
-- trigger which performs the foreign key integrity check, that NO check
-- should be performed at this stage.
--
mutation_prevention.fire_t
--
-- This is for debugging only and should be commented out on being satisfied
-- that the global variable is being set correctly.
--
debug_var:= mutation_prevention.fire_t
dbms_output.put_line(debug
--
-- Check to see which function is being performed, execute the appropriate
-- procedure in the package and the RESET the value of the global variable
-- to 'YES' to allow subsequent integrity checks to be performed.
--
IF UPDATING THEN
manual_cascade.cascade_upd
mutation_prevention.fire_t
--
-- Debugging only, check RESET of global variable.
--
debug_var:= mutation_prevention.fire_t
dbms_output.put_line(debug
--
-- Check the results from the executed procedure .. act accordingly, If this
-- was a BEFORE update Trigger the OLD value for DEPTNO could be rest if
-- required.
--
IF l_result = TRUE THEN
-- :new.deptno := :old.deptno;
RAISE transaction_failed;
END IF;
END IF;
EXCEPTION
WHEN transaction_failed THEN
raise_application_error(-2
'Update Of Department '||to_char(:old.deptno)||'
WHEN Others THEN
raise_application_error(-2
END;
/
/*
This trigger performs the integrity checking to validate that the foreign key
constraint is NOT violated when inserting and new employee or updating an
employees department.
*/
CREATE OR replace TRIGGER EMP_DEPT_CHECK
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE
l_fire_trigger varchar2(5);
dummy number(5);
--
-- Declare a mutating table EXCEPTION, however if this solution works this
-- should never be invoked, but has been included for completeness.
--
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table, -4091);
BEGIN
--
-- Check the Global variable, and either continue processing or EXIT.
--
l_fire_trigger := mutation_prevention.fire_t
IF l_fire_trigger = 'YES' THEN
BEGIN
Select 1
into dummy
FROM dept
WHERE deptno = :new.deptno;
IF dummy != 1 THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-2
'Department '||to_char(:new.deptno)||'
WHEN mutating_table then
raise_application_error(-2
'Table is MUTATING !!!!!!');
END;
END IF;
END;
/
/*************************
------------- 1. Trigger on PARENT for cascade function
| | SET global_variable = 'NO';
| PARENT | do cascade update;
| | SET global_variable = 'YES';
------------- End;
| -------------------
| | GLOBAL_VARIABLE |
| -------------------
/|\
------------ 2. Trigger on CHILD to maintain referential integrity
| | CHECK global_variable;
| CHILD | IF global_variable = 'YES' THEN
| | check existence of (fk) in PARENT;
------------ ELSE
null;
End;
**************************
of the following functional capabilities whilst being able to maintain
referential integrity among objects:
o Cascade Update
o Cascade Delete
o Cascade Insert
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: "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.
ORA-04091 is a very common error that occurs with triggers if triggers are not
managed properly. A full understanding of triggers will help you avoid that
error.
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.
Another way this error can occur is if the trigger has 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 following code illustrates how this is achieved for the cascade Update
scenario, the code can easily be modified to add the other functionality
and encapsulate it all within the same package. The EMP and DEPT table have
been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO
in the DEPT table.
/*************************
CREATE TABLE DEPT
(DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(30),
LOC VARCHAR2(30))
/
CREATE TABLE EMP
(EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(30) NOT NULL,
SAL NUMBER,
JOB VARCHAR2(30),
DEPTNO NUMBER))
/
**************************
/*
The mutation_prevention package is the KEY to the whole solution, it contains
only one variable which will indicate whether integrity checks should be
carried out as a result of an Insert or Update on the foreign key in the EMP
table.
*/
CREATE or replace PACKAGE mutation_prevention AS
fire_trigger varchar2(5) := 'YES';
END mutation_prevention;
/
/*
The package manual_cascade is a general purpose package which can also handle
Insert and Delete functionality
*/
CREATE or replace PACKAGE manual_cascade AS
PROCEDURE cascade_update(old_key IN NUMBER,
new_key IN NUMBER,
result OUT BOOLEAN);
END manual_cascade;
/
/*
The package body contains the procedures which will handle all the cascade
functionality, each will accept 3 parameters :
1. old_key -- The Old value which is used to reference CHILD records.
2. new_key -- The New value which is being inserted or substituted.
3. result -- Boolean returning the result of the operation.
Procedures contained in this package will be called by the trigger on the
parent table [call_manual_cascade].
*/
CREATE or replace PACKAGE BODY manual_cascade AS
PROCEDURE cascade_update(old_key IN NUMBER,
new_key IN NUMBER,
result OUT BOOLEAN) IS
loop_count NUMBER;
dummy_2 NUMBER;
dummy_3 NUMBER;
l_result BOOLEAN := false;
l_old_key NUMBER;
l_new_key NUMBER;
--
-- Declare cursor c1 to lock all child records which may be related to the
-- PARENT record. The NOWAIT is specified such that the procedure does NOT
-- continue to wait if these records are already locked by another user.
--
CURSOR c1 (l_dept NUMBER) IS
SELECT empno, deptno FROM EMP
WHERE deptno = l_dept
FOR UPDATE OF DEPTNO NOWAIT;
--
-- Declare exceptions to gracefully handle the CURSOR irregularities.
--
RESOURCE_BUSY ExCEPTION;
INVALID_CURSOR ExCEPTION;
PRAGMA EXCEPTION_INIT(RESOURCE_BU
PRAGMA EXCEPTION_INIT(INVALID_CUR
BEGIN
--
-- Assign input variables to local variables.
--
l_old_key := old_key;
l_new_key := new_key;
--
-- Check to see if there are any related child records to be updated. If NOT
-- then exit the IF TEST, otherwise proceed in locking the rows.
--
SELECT count(*) INTO loop_count
FROM EMP
WHERE deptno = l_old_key;
IF loop_count >= 1 THEN
--
-- At this stage you could define an REPEAT LOOP which will retry this
-- transaction in case of failure to lock all CHILD records successfully
-- [n] time. This concept is detailed in the PL/SQL manual V2.0 page 5-23
-- in the Error Handling section.
--
BEGIN
--
-- Open the cursor and handle any Error Conditions.
--
OPEN c1 (l_old_key);
IF NOT (c1%ISOPEN) THEN
RAISE INVALID_CURSOR;
END IF;
--
-- If successful then loop and update each row, one at a time until no more
-- rows. Handle Exceptions and Close the cursor.
--
FOR i IN 1..loop_count LOOP
FETCH c1 INTO dummy_2, dummy_3;
UPDATE emp SET deptno = l_new_key WHERE CURRENT OF c1;
END LOOP;
EXCEPTION
WHEN INVALID_CURSOR THEN
result := true;
WHEN RESOURCE_BUSY THEN
result := true;
WHEN OTHERS THEN
raise_application_error(-2
CLOSE c1; ----- ?
END;
END IF;
END cascade_update;
END manual_cascade;
/
/*
This trigger is on the PARENT table DEPT and controls the value of the global
variable [mutation_prevention.fire_
duration of the user session.
*/
CREATE or replace TRIGGER call_manual_cascade
AFTER UPDATE OF deptno ON DEPT
FOR EACH ROW
DECLARE
l_result BOOLEAN;
transaction_failed EXCEPTION;
debug_var varchar2(5);
BEGIN
--
-- Set the global control variable indicating to the [TRIGGER EMP_DEPT_CHECK]
-- trigger which performs the foreign key integrity check, that NO check
-- should be performed at this stage.
--
mutation_prevention.fire_t
--
-- This is for debugging only and should be commented out on being satisfied
-- that the global variable is being set correctly.
--
debug_var:= mutation_prevention.fire_t
dbms_output.put_line(debug
--
-- Check to see which function is being performed, execute the appropriate
-- procedure in the package and the RESET the value of the global variable
-- to 'YES' to allow subsequent integrity checks to be performed.
--
IF UPDATING THEN
manual_cascade.cascade_upd
mutation_prevention.fire_t
--
-- Debugging only, check RESET of global variable.
--
debug_var:= mutation_prevention.fire_t
dbms_output.put_line(debug
--
-- Check the results from the executed procedure .. act accordingly, If this
-- was a BEFORE update Trigger the OLD value for DEPTNO could be rest if
-- required.
--
IF l_result = TRUE THEN
-- :new.deptno := :old.deptno;
RAISE transaction_failed;
END IF;
END IF;
EXCEPTION
WHEN transaction_failed THEN
raise_application_error(-2
'Update Of Department '||to_char(:old.deptno)||'
WHEN Others THEN
raise_application_error(-2
END;
/
/*
This trigger performs the integrity checking to validate that the foreign key
constraint is NOT violated when inserting and new employee or updating an
employees department.
*/
CREATE OR replace TRIGGER EMP_DEPT_CHECK
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE
l_fire_trigger varchar2(5);
dummy number(5);
--
-- Declare a mutating table EXCEPTION, however if this solution works this
-- should never be invoked, but has been included for completeness.
--
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table, -4091);
BEGIN
--
-- Check the Global variable, and either continue processing or EXIT.
--
l_fire_trigger := mutation_prevention.fire_t
IF l_fire_trigger = 'YES' THEN
BEGIN
Select 1
into dummy
FROM dept
WHERE deptno = :new.deptno;
IF dummy != 1 THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-2
'Department '||to_char(:new.deptno)||'
WHEN mutating_table then
raise_application_error(-2
'Table is MUTATING !!!!!!');
END;
END IF;
END;
/
/*************************
------------- 1. Trigger on PARENT for cascade function
| | SET global_variable = 'NO';
| PARENT | do cascade update;
| | SET global_variable = 'YES';
------------- End;
| -------------------
| | GLOBAL_VARIABLE |
| -------------------
/|\
------------ 2. Trigger on CHILD to maintain referential integrity
| | CHECK global_variable;
| CHILD | IF global_variable = 'YES' THEN
| | check existence of (fk) in PARENT;
------------ ELSE
null;
End;
**************************
Thanks Schwertner, for the explanation.
sora
sora
Hi,
Mutating tables - Autonomous transactions
When developping triggers, it is possible that your table becomes in a mutating state. That means that you try an action (select,insert,update,dele
A work around for some cases (mainly select) is to use an autonomous transaction. That means that the action that you want to perform will be done in another transaction in your current transaction, and so you avoid mutation of a table.
see note http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipId=621
and also
Complex Rules In Triggers
If you ever need to do complicated logic in a trigger you will no doubt encounter the mutating table error. The standard workaround is to use packages and a combination of statement level triggers and row level triggers. Not fun to write, less fun to maintain.
An easier workaround that has helped me numerous times is the use of a view and instead of triggers. I rename the table and then create a view with the name of the original table. This way the application uses the view. You can then put INSTEAD OF triggers on the view. They can perform operations on the base table without fear of a mutating table error. A side benefit is that you can bypass the triggers if you so desire by hitting the base table directly. No need to disable/enable the triggers.
Date: Jun, 14 2002 Published by: Gord Jensen
solutions are really nice and need Oracle 8i and above.... If you are interested I can give you some examples.
best regards,
Marius Nicoras
Mutating tables - Autonomous transactions
When developping triggers, it is possible that your table becomes in a mutating state. That means that you try an action (select,insert,update,dele
A work around for some cases (mainly select) is to use an autonomous transaction. That means that the action that you want to perform will be done in another transaction in your current transaction, and so you avoid mutation of a table.
see note http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipId=621
and also
Complex Rules In Triggers
If you ever need to do complicated logic in a trigger you will no doubt encounter the mutating table error. The standard workaround is to use packages and a combination of statement level triggers and row level triggers. Not fun to write, less fun to maintain.
An easier workaround that has helped me numerous times is the use of a view and instead of triggers. I rename the table and then create a view with the name of the original table. This way the application uses the view. You can then put INSTEAD OF triggers on the view. They can perform operations on the base table without fear of a mutating table error. A side benefit is that you can bypass the triggers if you so desire by hitting the base table directly. No need to disable/enable the triggers.
Date: Jun, 14 2002 Published by: Gord Jensen
solutions are really nice and need Oracle 8i and above.... If you are interested I can give you some examples.
best regards,
Marius Nicoras
ASKER
Thanks for all of the information, it will take me some time to digest it and work out a solution to my problem. In the mean time, if anyone presents the code that makes MY example work, they will get the points.
hi,
if you have oracle 8i and above, add on your trigger code after
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
best regards,
Marius Nicoras
if you have oracle 8i and above, add on your trigger code after
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
best regards,
Marius Nicoras
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Ali,
I will not be able to test this until tonight, but I reviewed it and it is both logical and straight forward. After I test, I will award you the points.
Thanks for making it simple!
Mike
I will not be able to test this until tonight, but I reviewed it and it is both logical and straight forward. After I test, I will award you the points.
Thanks for making it simple!
Mike
ASKER
Thank you to EVERYONE for your expert comments, when I get time to digest all of the detail on this topic that you provided I will learn a lot.
I was offering so many points hoping for the solution that Ali provided. Thanks Ali, it worked like a champ. And I am comfortable giving you an A because if somebody else sees the problem and solution they will understand both, even if they are novice (like me on this topic).
I was offering so many points hoping for the solution that Ali provided. Thanks Ali, it worked like a champ. And I am comfortable giving you an A because if somebody else sees the problem and solution they will understand both, even if they are novice (like me on this topic).
Is it working OK? If an exception occurs in trig_User_Type_Insert, wouldn't the my_pkg.rec_from_usertype be incorrectly set to TRUE for the next operation?
ASKER
Thanks oracle_geek,
It is working, I modified Ali's code for my purposes and tested it well so I am sure everything is fine.
It is working, I modified Ali's code for my purposes and tested it well so I am sure everything is fine.
Mike, I just noticed the message you posted back in July when you recognized me. I somehow didn't see your message at the time.
Yes, I'm doing well. I'm at the job I took when I left WH and I still live where I did then, so my name and phone number are in the local phone book. Give me a call some evening when you get a chance.
Yes, I'm doing well. I'm at the job I took when I left WH and I still live where I did then, so my name and phone number are in the local phone book. Give me a call some evening when you get a chance.
There is a three-part workaround:
1. a holding area for rowids or record keys
2. a row-level trigger that stores the rowid(s) or record key(s) for each row changed
3. an "after-statement" trigger that retrieves the rowids or records keys and does the actual processing.
This works because "statement level" triggers (with no "for each row" clause) do not have the "mutating table" limitation, but they also do not know which, or how many records were just inserted, updated or deleted. For best performance the holding area for the rowids/record keys should be either:
1. a PL\SQL table, or
2. a global temporary table
Search the previously-answered questions for more detailed examples of getting around the mutating table error.