Re: trigger question
Date: 1996/03/29
Message-ID: <4jgr4m$n72_at_post.gsfc.nasa.gov>#1/1
In article <4jcsmq$snm_at_inet-nntp-gw-1.us.oracle.com>, tkyte_at_us.oracle.com (Thomas J Kyte) writes:
|> parris_at_duart.esp.bellcore.com (Parris Geiser) wrote:
|>
|> >Hi,
|> >We would like to execute a trigger when a record is
|> >inserted, but only if the transaction commits.
|> >That is, if the user inserts a record and then subsequently
|> >does a rollback, we don't want the trigger to fire.
|> >Is there any way to do this?
|> > Thanks for your help.
|> > parris
|>
|> You can't do this but it shouldn't matter.
|>
|> If the transaction that fired the trigger does a rollback, the work performed by
|> the trigger will rollback as well.
|>
|> If a user inserts a record and then subsequently does a rollback, it will be AS
|> IF the trigger didn't fire.
The problem I see is that what we need to do is to have a trigger which, upon
a row update, sends a message via dbms.pipes to a program so the program can perform
some related function outside of the database. What I've seen so far in answer to the
original query indicates a serious flaw in trigger usage for anything other than database
manipulation - i.e. if you trigger an action unrelated to the database and the original
action is rollbacked, you have no way of knowing that the rollback occured. So the
non-database action will be performed based on an action that was later rollbacked.
If I've interpreted this correctly, the only secure way to have a non-database action
performed based on a database change is to have the database change trigger an insertion
of a change flag (with any appropriate information) into a separate table
which another program monitors by constantly reading the table waiting for the change flag or
if possible, skip the trigger entirely and constantly check the original table for
any changes.
Joanne Woytek
joanne_at_daac.gsfc.nasa.gov
Received on Fri Mar 29 1996 - 00:00:00 CET