Home » SQL & PL/SQL » SQL & PL/SQL » Regarding mutating error on a table
Regarding mutating error on a table [message #270838] Fri, 28 September 2007 02:33 Go to next message
suneel99
Messages: 11
Registered: June 2007
Junior Member
Hello everybody,

Can anyone suggest me a workaround of the below error which is commonly occured when working on triggers.

I am creating a (after insert) trigger on OE_ORDER_HEADERS_ALL in which i am taking the newly created ORDER_NUMBER and updating the same in my custom table. The trigger is created without errors. But when i am trying to create a sales order, its not allowing me to go to the lineitems. Its giving a mutating error.

ORA-04091: table **** is mutating,trigger/function may not see it

I heard that a small code has to be appended to the trigger body as a workaround.

Thanks in advance,
Suneel.
Re: Regarding mutating error on a table [message #270841 is a reply to message #270838] Fri, 28 September 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you get this error, this means you have a flaw in your code.

Have a look at "returning" clause of insert statement.

Regards
Michel
Re: Regarding mutating error on a table [message #270848 is a reply to message #270841] Fri, 28 September 2007 02:56 Go to previous messageGo to next message
suneel99
Messages: 11
Registered: June 2007
Junior Member
Hi Michel,
There is no returning clause. I am just taking the newly inserted order_number from the base table and updating it into my custom table. Ther is no return value in the code.

Thanks & Regards,
Suneel.
Re: Regarding mutating error on a table [message #270852 is a reply to message #270848] Fri, 28 September 2007 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that this is a row level insert trigger, and you want to get a value from the record that has just been inserted, you can use the syntax :new.<column_name> to refer to the values of the new record.
Re: Regarding mutating error on a table [message #270854 is a reply to message #270848] Fri, 28 September 2007 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check syntax of INSERT statement. It includes a returning clause that allw to get values from the newly inserted row.
No trigger needed.

Regards
Michel
Re: Regarding mutating error on a table [message #270912 is a reply to message #270854] Fri, 28 September 2007 06:24 Go to previous message
suneel99
Messages: 11
Registered: June 2007
Junior Member
I am trying out with the same. Thanks for the reply.

Regards,
Suneel.
Previous Topic: Change Precisssion
Next Topic: package chk_pack
Goto Forum:
  


Current Time: Tue Dec 06 12:06:04 CST 2016

Total time taken to generate the page: 0.07531 seconds