Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA AUTONOMOUS_TRANSACTION
PRAGMA AUTONOMOUS_TRANSACTION [message #224407] Wed, 14 March 2007 03:24 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
i want to select and update from a table in the same time, for that reason I've got the Mutating error..i do not want to write more than one trigger on the table to solve the problem and no packages.
and because i need to update just one column so my question to you is:
Is the PRAGMA AUTONOMOUS_TRANSACTION the main solution to the Mutating table problem? and can i use such solution in one After insert or update trigger ??

Thanks in Advance,
bahaa
Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224410 is a reply to message #224407] Wed, 14 March 2007 03:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No. Absolutely a bad idea. An autonomous transaction is final and cannot be undone if the actual DML fails.
THE ways around a mutating table problem are a redesign (often a mutating table problem is the result of a design-flaw) or using multiple triggers and package.
Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224716 is a reply to message #224410] Thu, 15 March 2007 04:11 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
How can i solve the Mutating Error from happening in a case of selecting and updating the same table at the same time??


Thanks in Advance,
bahaa
Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224764 is a reply to message #224716] Thu, 15 March 2007 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to create a package which will record the record id's that you passit.
In a Before Update trigger, you pass in the id of each row that you are updating.
In an After Update trigger (at statement level) you then go through these recorded row ids one at a time and do your processing on them.
Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224950 is a reply to message #224764] Fri, 16 March 2007 02:17 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Link to an old thread.

MHE
Previous Topic: sending excel files as attachment using utl_smtp pkg.
Next Topic: ORA-03001 while using bulk collect
Goto Forum:
  


Current Time: Sun Dec 04 21:00:05 CST 2016

Total time taken to generate the page: 0.08807 seconds