Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Mutating issue
Trigger Mutating issue [message #194326] Thu, 21 September 2006 09:17 Go to next message
rravindhran
Messages: 7
Registered: March 2005
Location: India
Junior Member
Hi:

On update trigger, I have a condition, if the condition fails I want to insert a record into the same table. I am getting Mutating trigger error.

Any help on this issue will be appreciated.

Thanks in advance,
Ravindhran R
Re: Trigger Mutating issue [message #194329 is a reply to message #194326] Thu, 21 September 2006 09:37 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look here, so you learn why the error occurs

http://articles.techrepublic.com.com/5100-10878_11-5034684.html#

Re: Trigger Mutating issue [message #194357 is a reply to message #194326] Thu, 21 September 2006 11:52 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
This kind of mutation typically means that you should be using another table for secondary inserts.

However, in very rare cases when the operation represents a genuine requirement, and should be performed, a common trick to solve it is:

1. create a global temporary table that's deleted on commit/rollback (so values stored in global temporary tables are will shared between different database sessions, and each session will have a 'local' table).

2. create 2 triggers for update on that table, one executing for each row, and one executing after statement. in the first, insert rows into the temporary table; in second, transfer rows from temporary table to original table.

Gojko Adzic
http://www.gojko.com
Re: Trigger Mutating issue [message #198022 is a reply to message #194326] Fri, 13 October 2006 19:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
there are write ups here on ORAFAQ about this problem and how to work around it. Have a look around.

Good luck, Kevin
Re: Trigger Mutating issue [message #198064 is a reply to message #198022] Sat, 14 October 2006 03:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The search button still works. Here's an old thread addressing your issue.

MHE
Re: Trigger Mutating issue [message #198098 is a reply to message #194326] Sat, 14 October 2006 18:30 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi
This link may be of use to you. It shows how to get around mutating tables without having to use autonomous transactions

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

Razz
Previous Topic: i have logged on but it still report" ORA-01012: not logged on"
Next Topic: sql query
Goto Forum:
  


Current Time: Sat Dec 03 22:19:19 CST 2016

Total time taken to generate the page: 0.10051 seconds