Re: Before update trigger causing another after trigger to fire, returning NULL, causing before trigger to not update - does this make sense?

Susan Cassidy <scassidy(at)stbernard(dot)com> writes:
> Sequence of events when problem occurred:
> update table a
> causes update table b
> which updates table a again (different column)
> trigger for table b returns null
> update of table a does not happen

Different column of same row, you mean? Yes, I think that would cause
the outer update to not happen after all. By the time control returns
from the trigger, the intended target tuple of the outer update is dead
(having been already obsoleted by the inner update). The interpretation
of such a situation is to do nothing.

In general, having BEFORE triggers cause updates of other rows is bad
design. The rule of thumb is that BEFORE triggers should validate
and/or adjust the row you are about to store, while AFTER triggers are
preferred for propagating information from such an event to other rows
(whether in the same table or different ones). The usual argument for
that is that a BEFORE trigger can't be completely sure what the finally
stored state is going to be; but this sort of loop is another reason not
to do it.

regards, tom lane