Before update trigger causing another after trigger to fire, returning NULL, causing before trigger to not update - does this make sense?
Hi,
I was trying to update certain columns in a table, but the update never took place, but did not throw an exception. I investigated the code in the 'before update' trigger, and put in some RAISE NOTICE statements. I could then see that another update of the same table was happening, too, but the last return values in the last (outer) NEW record returned by the before trigger seemed fine. I then found that an additional UPDATE statement in the original 'before update' trigger was firing an 'after update' trigger on another table, which returned NULL.
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
After eliminating the call to the UPDATE statement for the second table (so that no NULL was returned), the original trigger started working as desired (e.g. columns were updated).
I know the documentation says 'If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.' However, the NULL was returned by an AFTER trigger, and not a BEFORE trigger, but it was within the cascade of events generated by the BEFORE trigger. This is Postgres 8.3.9 (I know, but upgrading would be a major undertaking).
The combination of data I was trying to update cannot occur with the current set of triggers, etc., so it is not unreasonable that this particular combination of events was unforeseen when the triggers were written.
Could this have really caused my problem? Is this how it is supposed to work? Or, is my hypothesis that the NULL caused the update to fail just a coincidence, and I should do further debugging?
Thanks,
Susan
Susan Cassidy <scassidy@stbernard.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