RE: ora-04091, updating same table in trigger

  • From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • To: <wbfergus@xxxxxxxxx>, <Harvinder_Singh@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 22 Apr 2009 10:17:41 -0400

Bill, 
I'm pretty sure, you are mistaken.
You can not " 'COMMIT' statements inside the trigger".
And "PRAGMA AUTONOMOUS_TRANSACTION;" you used not "In the trigger itself", but 
in the stored procedure you called from the trigger.

Igor Neyman

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Bill Ferguson
Sent: Wed 4/22/2009 8:38 AM
To: Harvinder_Singh@xxxxxxxxx; Oracle-L Freelists
Subject: Re: ora-04091, updating same table in trigger
 
I've had to deal with that issue as well.

In my database, my workaround was taking some of the logic out of the
trigger and creating procedures that get called from the trigger.

In the trigger itself, I have to use the "PRAGMA
AUTONOMOUS_TRANSACTION;" declaration, and use several 'COMMIT'
statements inside the trigger, like before the calls to the
procedures, at the end of the trigger, etc. It took a bit of
experimentation, but I finally got it all figured out and working
correctly.

This is on 11.1.0.6 on Windows Server 2003, so I'm not sure if this
will work the same  on other/previous versions or O/S's.

Hope this helps.

-- 
-- Bill Ferguson
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2009