Re: Trigger interplay

Thank you Sybrand for the time you spent to answer & explain. Using the pacakge did the trick, and I've got it working as desired now.

-Mia

Sybrand Bakker wrote:

> 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 Mon Oct 29 2001 - 13:16:04 CST