Florian Pflug <fgp@phlo.org> wrote:
> I think it would be helpful if we had a more precise idea about
> the intended use-cases. So far, the only use-case that has been
> described in detail is the one which made Kevin aware of the
> problem. But if I understood Kevin correctly, that fact that they
> use BEFORE and not AFTER triggers it more of an accident than the
> result of a conscious design decision.
For the UPDATE case we have so far only identified one place where
this was a problem -- although the grep we used would only show the
simplest form of this (where the UPDATE can be replaced by setting
fields in the NEW record). We've scheduled people to run through
the system test scripts again with my quick fix in place so that we
get an error rather than silent corruption (which might easily have
been missed the last time), so others could still turn up. In any
event, I have neither seen nor imagined any use cases in our shop
where we need to allow the UPDATE behavior.
> Though he did also mention that there might actually be advantages
> to using BEFORE instead of AFTER triggers, because that prevents
> other triggers from seeing a non-consistent state.
Right, although I've only seen that for the DELETE triggers. We are
using the BEFORE triggers to delete from the bottom up, and doing
this in the AFTER trigger would expose states to triggers where
children still exist in the absence of parents, which might fire
validation failures from hard-to-predict places. It would certainly
be more convenient on this end if the DELETE behavior from my patch
was accepted, but I'm confident that there are workarounds for the
long term if not.
> What I can add is that AFAIR all instances of same-row UPDATES
> from BEFORE triggers I ever encountered where replaceable by a
> simple assignment to NEW. (That, however, is more of an
> anti-usecase)
If there is a valid use-case for UPDATE, it would have to be less
direct -- for example, the BEFORE UPDATE trigger modifies some other
table or row, and the trigger for *that* updates the original row.
I really can't see any excuse for a BEFORE UPDATE ... FOR EACH ROW
trigger to execute a new UPDATE statement referencing the row for
which it is being fired.
-Kevin