Sean Davis <[EMAIL PROTECTED]> writes: > Just one detail, but in the form of a question. In the original > posting, I think the trigger was doing the logging for something > happening on a table as a before insert or update--I may be wrong on > that detail. I would think of doing such actions AFTER the > update/insert. In the world of transaction-safe operations, is there > ANY danger in doing the logging as a BEFORE trigger rather than an > AFTER trigger?
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. AFAICS the only way that you could get into a can't-roll-back situation is if the trigger tries to propagate the update outside the database. For instance, the proverbial trigger to send mail: once sent you can't cancel it. But really this is dangerous even in an AFTER trigger --- the transaction could still be rolled back after the AFTER trigger fires. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]