OraFAQ Forum: SQL & PL/SQL » PRAGMA AUTONOMOUS

Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA AUTONOMOUS_TRANSACTION

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PRAGMA AUTONOMOUS_TRANSACTION [message #224407]

Wed, 14 March 2007 03:24

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

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224410 is a reply to message #224407]

Wed, 14 March 2007 03:28

Frank
Messages: 7901
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.

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224716 is a reply to message #224410]

Thu, 15 March 2007 04:11

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

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224764 is a reply to message #224716]

Thu, 15 March 2007 07:04

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.

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION [message #224950 is a reply to message #224764]

Fri, 16 March 2007 02:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Link to an old thread.

MHE

Report message to a moderator

Previous Topic:	sending excel files as attachment using utl_smtp pkg.
Next Topic:	OUTER JOINS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 05:46:28 CST 2024