Re: trigger question
Date: 1996/03/29
Message-ID: <4jgtm2$d9o_at_inet-nntp-gw-1.us.oracle.com>#1/1
joanne_at_eosdata.gsfc.nasa.gov (Joanne Woytek) wrote:
>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.
>Up until now I was looking towards triggers as a good means of coordinating database actions
>and non-database reactions, but it looks like I'll have to go with less elegant solutions.
>Joanne Woytek
>joanne_at_daac.gsfc.nasa.gov
Have you looked at database alerts (dbms_alert). They are transactional and work somewhat like unix signals. If a trigger fires an ALERT and rollbacks, the ALERT does not get sent. If the trigger fires an ALERT and commits, the alert will get sent. You can use dbms_pipes (to send out large chunks of information) to a process that is waiting for an alert to tell him whether to throw it away or to keep it and process it. Or, you can just use ALERTS (they allow you to send upto 2k of stuff with the event itself).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Fri Mar 29 1996 - 00:00:00 CET