-
Hai Friends,
Any one help me out?...
I have a written a trigger for a table.. I got an error at the time of testing that trigger...
The error gives as
ORA-04091: table REF_COST_CENTRE is mutating, trigger/function may not see it
ORA-06512: at T_DU_REF_COST_CENTRE", line 41
ORA-04088: error during execution of trigger T_DU_REF_COST_CENTRE'
I know that mutating means... we are updating a row, which is selected by the trigger...
How to overcome this problem..
My trigger is
CREATE OR REPLACE TRIGGER T_DU_REF_COST_CENTRE
BEFORE DELETE OR UPDATE ON REF_COST_CENTRE FOR EACH ROW
DECLARE
lv_numrows INTEGER;
lv_numrowsmst INTEGER;
BEGIN
IF DELETING THEN
SELECT COUNT(*) INTO lv_numrows
FROM M_BP_COST_CENTRE
WHERE IE_COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;
IF lv_numrows > 0 THEN
raise_application_error(-20002,
'CANNOT DELETE FROM REF_COST_CENTRE TABLE
BECAUSE VALUE IS EXISTING IN
M_BP_COST_CENTRE TABLE');
END IF;
END IF;
IF UPDATING THEN
IF :NEW.COST_CENTRE_CODE
<> :OLD.COST_CENTRE_CODE THEN
SELECT COUNT(*) INTO lv_numrows
FROM M_BP_COST_CENTRE
WHERE IE_COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;
IF lv_numrows > 0 THEN
SELECT COUNT(*) INTO lv_numrowsmst
FROM REF_COST_CENTRE
WHERE COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;
IF lv_numrowsmst = 1 THEN
raise_application_error(-20002,
'CANNOT UPDATE REF_COST_CENTRE TABLE
BECAUSE VALUE IS EXISTING IN
M_BP_COST_CENTRE TABLE');
END IF;
END IF;
END IF;
Thanx..
Palani Kumar. R
-
Hi,
How to overcome this problem..
1. The best way would be using Foreign key instead of triggers.
Oracle referential integrity provides the same functionality as your trigger.
2. If you insist on using triggers, then remove statement causing error.
If you are trying to delete a row and your trigger is BEFORE delete, you can be sure that in the table REF_COST_CENTRE is at least one row with COST_CENTRE_CODE and COUNTRYCODE you write in WHERE clause - the row you are deleting.
Thus, you don't need to test it.
Regards,
Ales
-
Mutating Error
Hai Ales,
Thanks for your Answer...
I need more clarification about your Answer..
Thanx.
Palani Kumar. R
palanir@sakinfotech.co.in
-
There's lack of information about your tables to expand on that.
Post definition of tables, constraints ...
Why do you count rows in REF_COST_CENTRE table when UPDATING a row?
I guess that's useless and even causing the ORA-04091 error:
IF lv_numrows > 0 THEN
SELECT COUNT(*) INTO lv_numrowsmst
FROM REF_COST_CENTRE
WHERE COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;
-
Mutating table errors are caused by a trigger attempting to run DML against a table which is undergoing DML (mutating table error.
Basically, here's how you get around it:
1. Create a package. In the package, declare a PL/SQL table of the PK value or rowid from the table undergoing the DML.
2. Create a before statement trigger on the table undergoing DML, which initializes the PL/SQL table.
3. Create a row trigger which populates the PL/SQL table with the rows you'd like to update/process in a way that was causing the mutating table error.
4. Create an after statement trigger. In this trigger, process the rows of the PL/SQL table and do whatever action you need. The mutation of the table is over, so the DML is now allowed.
http://technet.oracle.com/doc/oracle...3trg.htm#11808
Here's an example from Metalink:
Doc ID: Note:37906.1
Subject: ORACLE MUTATING TABLE PROBLEM
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 31-OCT-1996
Last Revision Date: 08-MAR-2000
ORACLE MUTATING TABLE PROBLEM
=============================
This paper and the associated programs are offered to the public domain for
enhancement and research by an Oracle customer, Arup Nanda. No warranty is
either expressed or implied. The end user is solely responsible for results
of the execution of the programs.
PROBLEM:
-------
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(-20000,'Overlapping Dates');
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_tab(hold_grade) := hold_start_date;
salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
salgrade_pkg.changed_grade(hold_grade) := 'N';
end loop;
salgrade_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_salgrade
after insert on salgrade
for each row
begin
if (:new.grade <= salgrade_pkg.start_date_tab_size) then
if salgrade_pkg.start_date_tab(:new.grade)
> :new.start_date then
raise_application_error(-20001,'Overlapping Dates');
end if;
salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
salgrade_pkg.changed_grade(:new.grade) := 'Y';
else
salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
salgrade_pkg.changed_grade(:new.grade) := 'N';
salgrade_pkg.start_date_tab_size :=
salgrade_pkg.start_date_tab_size + 1;
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_date_tab_size) loop
if (salgrade_pkg.changed_grade(i) = 'Y') then
update salgrade
set end_date = salgrade_pkg.end_date_tab(i)
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.
-
Quote:
Originally posted by kmesser
Mutating table errors are caused by a trigger attempting to run DML against a table which is undergoing DML (mutating table error.
Basically, here's how you get around it:
1. Create a package. In the package, declare a PL/SQL table of the PK value or rowid from the table undergoing the DML.
2. Create a before statement trigger on the table undergoing DML, which initializes the PL/SQL table.
3. Create a row trigger which populates the PL/SQL table with the rows you'd like to update/process in a way that was causing the mutating table error.
4. Create an after statement trigger. In this trigger, process the rows of the PL/SQL table and do whatever action you need. The mutation of the table is over, so the DML is now allowed.
http://technet.oracle.com/doc/oracle...3trg.htm#11808
Here's an example from Metalink:
Doc ID: Note:37906.1
Subject: ORACLE MUTATING TABLE PROBLEM
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 31-OCT-1996
Last Revision Date: 08-MAR-2000
ORACLE MUTATING TABLE PROBLEM
=============================
This paper and the associated programs are offered to the public domain for
enhancement and research by an Oracle customer, Arup Nanda. No warranty is
either expressed or implied. The end user is solely responsible for results
of the execution of the programs.
PROBLEM:
-------
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(-20000,'Overlapping Dates');
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_tab(hold_grade) := hold_start_date;
salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
salgrade_pkg.changed_grade(hold_grade) := 'N';
end loop;
salgrade_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_salgrade
after insert on salgrade
for each row
begin
if (:new.grade <= salgrade_pkg.start_date_tab_size) then
if salgrade_pkg.start_date_tab(:new.grade)
> :new.start_date then
raise_application_error(-20001,'Overlapping Dates');
end if;
salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
salgrade_pkg.changed_grade(:new.grade) := 'Y';
else
salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
salgrade_pkg.changed_grade(:new.grade) := 'N';
salgrade_pkg.start_date_tab_size :=
salgrade_pkg.start_date_tab_size + 1;
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_date_tab_size) loop
if (salgrade_pkg.changed_grade(i) = 'Y') then
update salgrade
set end_date = salgrade_pkg.end_date_tab(i)
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.
If you are using Oracle 8i version and above then you can use materialized views to avoid this mutating problem.
This is another alternative.
Regards,
Vel
