Re: ora-04091, updating same table in trigger

  • From: Bill Ferguson <wbfergus@xxxxxxxxx>
  • To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>
  • Date: Wed, 22 Apr 2009 12:53:41 -0600

Igor,

Upon further reflection, you are correct.

I should have stated a slight difference. I have INSTEAD_OF triggers
on views, which need to commit data in several tables (and in a few
cases, against other views - UGLY), which in turn have triggers on
them as well. The regular 'table' triggers don't have the commits, but
the view triggers do.



On Wed, Apr 22, 2009 at 8:17 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
>
> 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
>
>
>
>
>



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

Other related posts:

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