Home » SQL & PL/SQL » SQL & PL/SQL » mutating problem
mutating problem [message #273521] Wed, 10 October 2007 14:32 Go to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
i was trying to create a trigger which involves two tables.Ones' table's data is a foreign key to another table.So when this data is updated,it shouldnt exceed the the correspoding column value in the parent table.So i created a trigger which fires when the data is to be changed.But i have an mutating table error.Code is given below.
create or replace
TRIGGER TR_EXCEEDINGLIMITNOTICE
 AFTER INSERT OR UPDATE of expamt ON EXPENSEITEM 
 FOR EACH ROW 
 DECLARE

 amt NUMBER;
 num number;
invalid_amt EXCEPTION;
begin
select ecno into num from expenseitem;
  SELECT eclimit INTO amt FROM expcat where expcat.ecno=num;
  IF((:new.expamt > amt)) THEN
    RAISE invalid_amt;
  END IF;

EXCEPTION
WHEN invalid_amt THEN
  raise_application_error(-20444,   'expense amount
 should be lesser than category limit');
END TR_EXCEEDINGLIMITNOTICE;

and i i tried to change the data by
 update expenseitem set expamt=100 where 
expdesc='Hilton';

then i got error " the table expenseitem is mutating.trigger or function may not see it.A trigger is trying to look at a table that was in the middle of being modified by the statement which fired it."
This is the error.What should i do?
Many thanks
ryju
Re: mutating problem [message #273530 is a reply to message #273521] Wed, 10 October 2007 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a procedure to make your insert and/or update instead of trying to do it through a trigger then you can do what you want.

Regards
Michel
Re: mutating problem [message #273542 is a reply to message #273521] Wed, 10 October 2007 16:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The mutation problem happens when you try to select from the same table that the trigger is on. Use :new values instead, as shown below.


create or replace
TRIGGER TR_EXCEEDINGLIMITNOTICE
  AFTER INSERT OR UPDATE of expamt ON EXPENSEITEM 
  FOR EACH ROW 
DECLARE
  amt         NUMBER;
  invalid_amt EXCEPTION;
begin
  SELECT eclimit 
  INTO   amt 
  FROM   expcat 
  where  expcat.ecno = :new.ecno;
  IF((:new.expamt > amt)) THEN
    RAISE invalid_amt;
  END IF;

EXCEPTION
WHEN invalid_amt THEN
  raise_application_error(-20444,   'expense amount
 should be less than category limit');
END TR_EXCEEDINGLIMITNOTICE;
/


Re: mutating problem [message #273544 is a reply to message #273521] Wed, 10 October 2007 19:10 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
sorry.It still doesnt work.Same error.Thanks anyway
cheers
Ryju
Re: mutating problem [message #273546 is a reply to message #273521] Wed, 10 October 2007 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post the whole SQL*Plus session showing your code & Oracle's response.
Re: mutating problem [message #274029 is a reply to message #273544] Fri, 12 October 2007 15:40 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
RyjuRaju wrote on Wed, 10 October 2007 20:10

Hi
sorry.It still doesnt work.Same error.Thanks anyway
cheers
Ryju


You are still SELECTing from the same table.
Re: mutating problem [message #275067 is a reply to message #274029] Thu, 18 October 2007 04:02 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Try

Autonomous transaction in your trigger ....

This can help to rid out of the mutation error.

Re: mutating problem [message #275075 is a reply to message #275067] Thu, 18 October 2007 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you will have incorrect and unexpected results.

Regards
Michel
Re: mutating problem [message #275083 is a reply to message #275075] Thu, 18 October 2007 04:17 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
It is a matter of fact that how you use ... If you use it right way than you get it right else wrong.
Re: mutating problem [message #275088 is a reply to message #275083] Thu, 18 October 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a matter of fact that if you use autonmous transaction to eliminate mutation error you WILL have inconsistent data.
What is your opinion: why Oracle generates this error? Just to make your work harder?

Regards
Michel
Re: mutating problem [message #275091 is a reply to message #275088] Thu, 18 October 2007 04:37 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Yes Michel,
I do agree with you .. Even I will not recommend tigger using the same table. Even Autonomous will make it more hard to control. But there are some you have times when you have to use these complicated elements.

Can you please recommend some other method to remove this error. Hope this will help to suggest some one to use your option other than Autonomous.

Regards
Trivendra
Re: mutating problem [message #275093 is a reply to message #275091] Thu, 18 October 2007 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is in my first post.
If you have complex thing to do, don't use update sql statement use a procedure that makes the whole stuff you want.
Then it is clear, it is sane, everyone (who are allowed) can see what happens, what is the logic, nothing is hidden, nothing is magic.

Regards
Michel
Re: mutating problem [message #275097 is a reply to message #275093] Thu, 18 October 2007 05:01 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel.
Re: mutating problem [message #275098 is a reply to message #273521] Thu, 18 October 2007 05:03 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi Ryju, I agree with Michel, using Autonomous Transactions will cause issues, for example when the user issues a rollback.
The article link below is a common method working around mutating tables.

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

Hope this helps
Previous Topic: Group function
Next Topic: How to know the information about packages that are getting executed in the database
Goto Forum:
  


Current Time: Sat Dec 10 03:28:26 CST 2016

Total time taken to generate the page: 0.20995 seconds