Re: Question on triggers and plpgsql

Sean Davis <sdavis2@mail.nih.gov> writes:
> Just one detail, but in the form of a question. In the original 
> posting, I think the trigger was doing the logging for something 
> happening on a table as a before insert or update--I may be wrong on 
> that detail.  I would think of doing such actions AFTER the 
> update/insert.  In the world of transaction-safe operations, is there 
> ANY danger in doing the logging as a BEFORE trigger rather than an 
> AFTER trigger?

No, actually Carlos wanted to donew.last_modified = now();
so he *must* use a BEFORE trigger --- AFTER is too late to change the
data that will be stored.

Generalizing freely, I've seen three basic uses for triggers:1. Modify the data that will be stored.2. Check that data
isvalid (eg, consistent with another table).3. Propagate updates in one place to other places.
 
Clearly #1 must be done in BEFORE triggers.  #2 and #3 could be done
either way.  They are often done in AFTER triggers because that way you
*know* that any case-1 triggers have done their work and you are looking
at the correct final state of the row.  But you could do them in a
BEFORE trigger if you were willing to assume that no later-fired trigger
would make a change that invalidates your check or propagation.  AFTER
triggers are relatively expensive (since the triggering event state has
to be saved and then recalled) so I could see making that tradeoff if
performance is critical.

AFAICS the only way that you could get into a can't-roll-back situation
is if the trigger tries to propagate the update outside the database.
For instance, the proverbial trigger to send mail: once sent you can't
cancel it.  But really this is dangerous even in an AFTER trigger ---
the transaction could still be rolled back after the AFTER trigger
fires.
        regards, tom lane