Tom,
Thanks for setting the record straight. It has been a while since I
have written a trigger and I forgot that you can't modify the row in
the AFTER trigger. Makes perfect sense.
For the record, here is what the docs say:
Typically, row before triggers are used for checking or modifying the
data that will be inserted or updated. For example, a before trigger
might be used to insert the current time into a timestamp column, or to
check that two elements of the row are consistent. Row after triggers
are most sensibly used to propagate the updates to other tables, or
make consistency checks against other tables. The reason for this
division of labor is that an after trigger can be certain it is seeing
the final value of the row, while a before trigger cannot; there might
be other before triggers firing after it. If you have no specific
reason to make a trigger before or after, the before case is more
efficient, since the information about the operation doesn't have to be
saved until end of statement.
It might be worth adding a sentence here that explicitly states
modifications can only be made in the BEFORE trigger. I did not see
that anywhere else in the document.
On Apr 8, 2005, at 10:36 AM, Tom Lane wrote:
> No, actually Carlos wanted to do
> new.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 is valid (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.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL