Home » SQL & PL/SQL » SQL & PL/SQL » Table is mutating, trigger/function may not see it
Table is mutating, trigger/function may not see it [message #265538] Thu, 06 September 2007 11:10 Go to next message
kelvinora
Messages: 16
Registered: September 2007
Location: India
Junior Member
Hi I got the below error..

ORA-04091: table TRIGGTEST.PY_TEST is mutating, trigger/function may not see it
ORA-06512: at "TRIGGTEST.BEFORE_TEST_TRIGGER", line 2
ORA-04088: error during execution of trigger 'TRIGGTEST.BEFORE_TEST_TRIGGER

What i am trying to do is....
When a py_test table contains same id means (1), i dont want to delete it from sy_test..
So what i am doing is before delete it from py_test,i can store the values to temp_table.
From this temp_table i had check the count and then performing the operations.

But i got the ORA-04091 error.

The below is my table structures and Triggers..


sql>create table py_test(id number,ename varchar2(20);
sql>create table sy_test(id number,ename varchar2(20);

sql>CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
ename varchar2(20)
) ON COMMIT DELETE ROWS;


sql>select * from py_test;

ID ENAME
1 one
2 two
1 three

sql>select * from sy_test;

ID ENAME
1 one
2 two


sql> create or replace trigger Before_test_trigger
before delete on py_test
for each row
begin
insert into temp_table (select * from py_test where id=:old.id);
end;

sql>Create Or Replace Trigger After_test_trigger
After delete on py_test
for each row

declare
idcount number(10);
begin
Select count(*) into idcount from temp_table where id=:old.id;
IF(idcount >= 2) THEN
insert into sy_test values(idcount,'Hai');

ELSIF(idcount <= 1) THEN
delete from sy_test where id=:old.id;
END IF;
end;

sql>delete from py_test where id=1;

How to solve this?

Thanks
Re: Table is mutating, trigger/function may not see it [message #265555 is a reply to message #265538] Thu, 06 September 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then,
a quick search gives you the answer: you can't select the table you currently modifying in the (for each row) trigger.
More it is useless, you have the data in the "old" record.

Regards
Michel
Re: Table is mutating, trigger/function may not see it [message #265711 is a reply to message #265555] Fri, 07 September 2007 02:11 Go to previous messageGo to next message
kelvinora
Messages: 16
Registered: September 2007
Location: India
Junior Member
Thanks for your suggestion

It is necessary to select the records form py_test table..
How i can i do this?

Thanks
Re: Table is mutating, trigger/function may not see it [message #265718 is a reply to message #265711] Fri, 07 September 2007 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
More it is useless, you have the data in the "old" record.

Quote:
:old.id

Regards
Michel
Re: Table is mutating, trigger/function may not see it [message #265722 is a reply to message #265718] Fri, 07 September 2007 03:04 Go to previous messageGo to next message
kelvinora
Messages: 16
Registered: September 2007
Location: India
Junior Member
Thanks

If i am using :old.id means only one record is get stored
like 1 one.
I want to strore
1 one
1 three also..
Thats why i had used select query..

Thanks
Re: Table is mutating, trigger/function may not see it [message #265729 is a reply to message #265722] Fri, 07 September 2007 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
If you have some logic in your delete then, don't use trigger use a procedure to implement your logic.

Regards
Michel
Re: Table is mutating, trigger/function may not see it [message #265741 is a reply to message #265729] Fri, 07 September 2007 04:07 Go to previous messageGo to next message
kelvinora
Messages: 16
Registered: September 2007
Location: India
Junior Member
Thanks for quick response..
Yes.. Stored Prcodeure is good solution.
But if i want to delete it manually means..how can i call Prcoedure?

Thanks
Re: Table is mutating, trigger/function may not see it [message #265784 is a reply to message #265741] Fri, 07 September 2007 06:58 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
exec myproc (its_parameters)

Regards
Michel

[Updated on: Fri, 07 September 2007 06:59]

Report message to a moderator

Previous Topic: MATERIALIZED VIEW PROBLEM
Next Topic: Query Hangs
Goto Forum:
  


Current Time: Sat Dec 10 06:49:05 CST 2016

Total time taken to generate the page: 0.15414 seconds