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

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

Search Discussions

  • Tom Lane at Dec 2, 2004 at 5:43 pm

    Terry Lee Tucker 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
postedDec 2, '04 at 5:05p
activeDec 2, '04 at 5:43p

2 users in discussion

Terry Lee Tucker: 1 post Tom Lane: 1 post



site design / logo © 2022 Grokbase