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@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