Home » SQL & PL/SQL » SQL & PL/SQL » Trigger mutation error
Trigger mutation error [message #196814] Sat, 07 October 2006 14:10 Go to next message
masskuth
Messages: 5
Registered: July 2006
Junior Member
Hi,

I have a problem here while using the trigger.

I have two tables ITEM and ITEM1 which are exactly the same. Description of table is as below but there is no data in ITEM1

Select * from item

CORR_ACC_NO | VALUE_DATE | ORDER_ID | AMOUNT | MATCH_NO | ITEM_QUEUE | ITEM_STATUS
1001 | 11-DEC-06 | 1 | 1000 | 0 | PURCHASED | OUTSTANDING


I am trying to create a Trigger when ever there is an insert in ITEM as below

insert in ITEM table (corr_acc_no,order_id,amount,item_queue) values (1001,1,1500,'C')

ie on inserting if 'ITEM_QUEUE' is 'C' then I would like to check if matching corr_acc_no and order_id already exists In that case I would like to insert a record in table 'ITEM1'with the existing matching row in item table. So as per above insert statement I would like to copy the first record in ITEM1 since item_queue is 'C' and matching corr_acc_no and order_id already exist in the table.

I have created a trigger as below but it is giving me an error.

CREATE OR REPLACE TRIGGER item_t1
AFTER INSERT ON ITEM
FOR EACH ROW
declare
corr_no number;
BEGIN
if :new.item_queue = 'C' then
select corr_acc_no into corr_no from item where (corr_acc_no,order_id) in (select corr_acc_no,order_id from item where item_queue = :new.item_queue) and item_queue <> :new.item_queue;
INSERT INTO ITEM1(CORR_ACC_NO) VALUES (corr_no);
end if;
END;


the error getting while inserting the statement is

ORA-04091: table recon.ITEM is mutating, trigger/function may not see it
ORA-06512: at "recon.ITEM_T1", line 5
ORA-04088: error during execution of trigger 'recon.ITEM_T1'

Can anyone please help me what may be the possible reason be.

Thanks
Raks

[Updated on: Sat, 07 October 2006 14:15]

Report message to a moderator

Re: Trigger mutation error [message #196815 is a reply to message #196814] Sat, 07 October 2006 14:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following article by Tom Kyte describes causes and solutions:

http://asktom.oracle.com/~tkyte/Mutate/index.html



Re: Trigger mutation error [message #196817 is a reply to message #196814] Sat, 07 October 2006 20:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are generally three ways to avoid this error. Tom's article covers the oldest know solution. Here are all three:

1) Use a group of triggers (Tom's article). Save data from row level triggers into work tables; then process the work tables in an after statement trigger.

2) Make a call to an function defined with the pragma autonomous transaction, to check on the state of your data. Although you can change data this way too, it is generally not a good idea because it makes for a split transaction, and this method has some limitations.

3) Use a view and instead of trigger to do the work you originally wanted to do, in a different context not subject to the mutating table error. This is by far the easiest and cleanest method. Once you get comfortable with instead of triggers, you won't want to go back. These operate much like after row triggers, but without the mutating table issues.

I can't compete with Tom so I'll let his words stand alone, but here are some code samples for #2, #3 to help you out.

Also, I have not tested any of this code. The methods are all sound. I leave it to you to find and fix any syntax or logic errors.

Method#2 (autonomous transaction)

create or replace
function get_corr_acc_no (item_queue_p in varchar2) return number
is
pragma autonomous_transaction;
rv number;
begin
begin
select corr_acc_no
into rv
from item
where (corr_acc_no,order_id) in
(
select
corr_acc_no
,order_id
from item
where item_queue = item_queue_p
)
and item_queue <> item_queue_p
;
exception when no_data_found then null;
end;
commit;
return rv;
end;
/
show errors

CREATE OR REPLACE TRIGGER item_t1
AFTER INSERT ON ITEM
FOR EACH ROW
declare
corr_no number;
BEGIN
if :new.item_queue = 'C' then
corr_no := get_corr_acc_no;
if corr_no is not null then
INSERT INTO ITEM1(CORR_ACC_NO) VALUES (corr_no);
end if;
end if;
END;
/
show errors

Your trigger now calls the function which is autonomous to check for the existence of a previous row). The idea here is that you do the lookup you wanted to do, just in another seperate transaction (eg. pragma autonomous_transaction). There is a limitation however on just what data the called function can see. I believe the rule is: the data it sees is the data before the start of your transaction. Thus if you want to do a check against data that was inserted as part of your any statements in your transaction, you can't. Thus you must know your transactions well for this to work. Updating data can also be problematic as witnessed by this sequence of code:

create table a (a number);

create or replace
procedure insert_another_a
is
pragma autonomous_transaction;
begin
--
-- insert this row but only if the table is empty
-- we are starting with an empty table
-- but your insert just put a row into it
-- wonder what will happen?
--
insert into a
select 2
from dual
where not exists
(
select null
from a
)
;
commit;
end;
/
show errors

create or replace
trigger ai_a
after insert on a
begin
insert_another_a;
end;
/
show errors

insert into a values (1);

SQL> insert into a values (1);
insert into a values (1)
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of trigger 'KEVIN.AI_A'
ORA-06512: at "KEVIN.INSERT_ANOTHER_A", line 5
ORA-06512: at "KEVIN.AI_A", line 2
ORA-04088: error during execution of

SQL>

boy is that ugly or what. Seems we can actually change table A even though it is mutating. But we can't see any of the changes we are making. You can see this by knowing that the autonomous transaction procedure won't insert anything unless the table is empty. But it thinks the table is always empty so it keeps inserting. It thinks this because 1) the table started empty, 2) you inserted a row, but it can't see that row, so it inserted its own row, 3) it can't even see the row it inserted so when it inserted its own row, that caused the tirgger to fire again which inserted another row (and round and round she goes). If you don't understand, then just look at the code and think about it for a while. This is one of the reasons it is not a good idea to change data in this circumstance; because you never know where you might end up in an infinite loop of changes as it were.

Method#3 (view with instead of trigger)

rename table ITEM to ITEM_original
/

create or replace
view item
as
select *
from item_original
/

CREATE OR REPLACE
TRIGGER item_t1
instead of INSERT ON ITEM
FOR EACH ROW
declare
corr_no number;
BEGIN
insert into item_original values (:new....);
if :new.item_queue = 'C' then
select corr_acc_no into corr_no from item where (corr_acc_no,order_id) in (select corr_acc_no,order_id from item where item_queue = :new.item_queue) and item_queue <> :new.item_queue;
INSERT INTO ITEM1(CORR_ACC_NO) VALUES (corr_no);
end if;
END
/

Using the view and instead of trigger makes your code execute altogether outside the context underwhich you were getting the mutating table error. Thus you can do anything you want, so we just use your original trigger as the inspiration for our instead of trigger and make sure that we do the original operation first (or after we check if that is want we want).

Instead of triggers are my #1 favorite feature of Oracle. They are I believe the most important and yet underutilized capability of the database. You can truely do magic with them. Check out a product called Oracle Workspace Manager when you get a chance. It is based on views and instead triggers, very powerful, very sophisticated, very useful under the right circumstances.

Good luck buddy. Kevin Meade
Re: Trigger mutation error [message #196989 is a reply to message #196814] Mon, 09 October 2006 08:20 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi
The article in the following link has an easy to follow solution to mutating tables.

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11010

Razz
Previous Topic: Timestamp of DML and DDL staatements
Next Topic: tables
Goto Forum:
  


Current Time: Thu Dec 08 16:12:55 CST 2016

Total time taken to generate the page: 0.08050 seconds