Re: Trigger interplay

1 You did understand me correctly
2 Using a table, which is no real part of the transaction, you will end up with locking issues, as you have to update that table continuously. The commit of your main transaction is also going to interfere with the 'transaction' on your 'third' table. That can probably be resolved using autonomous transaction, but in that case you will still use way more I/O than in my solution
3 Don't be misleaded by the word global. The variable will be global *within* the session context. Other sessions will have their own package context. A package context is never shared.

Hth,

Sybrand Bakker
Senior Oracle DBA

"Mia" <mmiaa_at_home.com> wrote in message news:3BD9AB36.3A84BDB8_at_home.com...
> Thanks Sybrand,
>
> Let me see if I understand you correctly. The package is used simply as a
> place to store a flag. The first trigger to fire (table B) sets the flag,
> indicating "It's me", then updates table A. The table A trigger checks
the
> flag, and conditionally allows the update.
>
> As I've not used packages before, I am interested in knowing what benefit
> this has over using a third table to store the flag. Also, as I have
multiple
> sessions that each could fire the triggers, what must I do to make sure
they
> don't step on eachother?
>
> -Mia
>
> Sybrand Bakker wrote:
>
> > "Mia" <mmiaa_at_home.com> wrote in message
news:3BD59FB6.C4ECF89C_at_home.com...
> > > I have an update trigger on table A that prevents a column from being
> > > changed. I have another trigger on table B that in some cases needs
to
> > > update a column on table A (the very value the first trigger
protects).
> > >
> > > How can I handle this? Can trigger A include some logic so that it
> > > "knows" when the update is coming from trigger B, and thus allow it?
> > >
> > > TIA,
> > > -Mia
> > >
> >
> > Sure
> > Create a package
> > Define a variable in the package spec.
> > This variable will determine from which source the update is coming.
> > So, trigger B has to modify the variable, before trigger A fires.
> >
> > Hth,
> >
> > Sybrand Bakker
> > Senior Oracle DBA
>
Received on Fri Oct 26 2001 - 14:28:05 CDT