Re: ora-04091, updating same table in trigger

  • From: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>
  • To: wbfergus@xxxxxxxxx
  • Date: Wed, 22 Apr 2009 16:15:14 +0200

Just be very careful when you do that. You are circumventing a key feature
of Oracle. If you understand exactly why the mutating transaction is
something good and you know for *SURE* what you do is completely safe with
how Oracle handles transaction integrity, then maybe. That workaround has
caused more than on case of strange data integrity issues. But if you really
know what you are doing, then it may be possible. Most of the time it is
taking a risk with some portion a databases concept of acidity and it can be
very hard to understand all sides of it. Thus it may be better to not play
with fire in almost all cases.

Just a warning, it may not have the exact effect you think (or someone
else). Like what happens if your transaction rolls back after the trigger
has comitted som data. Is that safe or would that cause a data intergity
issue?

It is often better to accept the pain of normalizing than the pain of
reduced data integrity.

Mathias

On Wed, Apr 22, 2009 at 2:38 PM, Bill Ferguson <wbfergus@xxxxxxxxx> wrote:

> 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