Re: Question on triggers and plpgsql

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