Re: a primer on trigger?

On Fri, 4 May 2001, Joel Burton wrote:

> Hmmm... this raises an interesting question.
>
> Would it be possible to hook into (via trigger or other mechanism) so that
> we could execute a function on commit? There are PG triggers to do things
> like send email, etc., which, yes, can't be undone if the transaction
> ultimately fails. Instead, could we:
>
> 1) have a trigger that (in this case) instead of sending email, just adds
> the information to an 'email-to-send' table.
>
> 2) commit the transaction
>
> 3) the trigger TRANSACTION_AFTER is called, and from that, we can scan the
> table, and actually do some of these system calls?
>
> Can this be done? Is this terrible design? Is there any other reasonable
> way to handle things like this?

Probably could be done, but the question would be what happens if the
TRANSACTION_AFTER raises an error condition for whatever reason? You
can't go back and un-commit the transaction.

For that case above, you'd probably be better off having something in cron
or whatever looking at your email-to-send table since it'll get only those
things that have already committed. You could put all the logic in a
function on the server still.