Re: trigger question

In article <4jgr4m$n72_at_post.gsfc.nasa.gov>, joanne_at_eosdata.gsfc.nasa.gov (Joanne Woytek) writes:
> 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.
<sig snip>

I needed to have certain users notified when certain values were changed in the db. I planned to use user exits to send mail from triggers, but was afraid the users would be overwhelmed w/ lots of short mail. So I just dump the data into a temp table, roll it up each night w/ a PRO*C program, and delete data older than a certain age. It is not elegant but, as I noted in an earlier post, once I lost my religion about how things _should be_ and got a grip on how _they are_, life for the users I support got a lot better. Do what works for your business, and keep looking for a better way to do it, IMHO. (I've noticed that in my environment there are very few rollbacks, so I guess I'm lucky.)

-- 
Scott A. Smith                      | These views are mind alone;
smiths_at_austin.ibm.com               | I _do not_ speak for IBM.
IBM RISC System/6000 Division       |
Information Design and Development  |