Обсуждение: Trigger Question

Hello:

I am in the process of converting trigger code written in Progress 4GL to
PostgreSQL triggers written in pl/pgsql. I have some Progress code in a
delete trigger that fires on the deletion of a money line item. In Progress,
I was setting the amount column to 0 in the delete trigger, which, in the
Progress world, caused one of the write triggers to fire before the delete
occured. The code to adjust balances in several places was already
implemented in that write trigger, so setting the amount to zero in the
delete trigger worked nicely; however, PostgreSQL doesn't behave that way.
I'm setting old.amount to zero in a BEFORE DELETE trigger, but it doesn't do
anything in terms of firing another trigger that operates directly on amount
during an update operation. I can work around this so the focus of my
question is this: 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? I
searched the documentation but didn't find anything that discussed the
differences between the two trigger types; that is, before and after delete
triggers.

Thanks for any input you can give...
--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Terry Lee Tucker <terry@esc1.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