RE: ora-04091, updating same table in trigger

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 23 Apr 2009 10:57:07 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2C98BBC_at_EXCHANGE.corp.perceptron.com>



Yechiel,

Is you login trigger calling stored procedure and does: "pragma auto.. and commits" in the stored procedure, or it's all done in the trigger itself?

Igor

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Yechiel Adar Sent: Thu 4/23/2009 5:20 AM
Cc: Oracle-L Freelists
Subject: Re: ora-04091, updating same table in trigger  

I think you are wrong or I misunderstood what you wrote.

I just checked my login trigger and it has the "pragma auto.." and commits. It works fine.

Adar Yechiel
Rechovot, Israel

Igor Neyman 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_at_freelists.org on behalf of Bill Ferguson
> Sent: Wed 4/22/2009 8:38 AM
> To: Harvinder_Singh_at_picis.com; 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 23 2009 - 09:57:07 CDT

Original text of this message