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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
