Re: Trigger Question

Terry Lee Tucker <terry(at)esc1(dot)com> writes:
> What would be the circumstances in which you would want to have a
> trigger that fires BEFORE the delete as opposed to AFTER the delete?

A BEFORE trigger can suppress the deletion of that individual row
(by returning NULL). An AFTER trigger cannot, other than by raising
an error to abort the whole transaction.

This isn't all that exciting for deletions, but for inserts and updates
it's a little more interesting: a BEFORE trigger can suppress the
individual insertion or update, or it can modify the row being inserted
or updated. This means that a series of BEFORE triggers can make
independent alterations of the data. An AFTER trigger can no longer
muck with the row, but on the other hand it knows that what it sees is
the row that actually got inserted or updated, whereas a BEFORE trigger
should never assume that it's the last one in the series.

So typically you use BEFORE triggers to modify the action that's about
to occur, whereas you use AFTER triggers to propagate the final result
elsewhere (for instance, make a copy in a logging table).

BTW, if you don't have any particular preference for a given trigger,
you should always use BEFORE. The AFTER case requires remembering the
trigger event till end of command and is thus inherently less efficient
(especially if the command affects many many rows).

regards, tom lane