Hi,
I was trying to update certain columns in a table, but the update never took place, but did not throw an exception. I
investigatedthe code in the 'before update' trigger, and put in some RAISE NOTICE statements. I could then see that
anotherupdate of the same table was happening, too, but the last return values in the last (outer) NEW record returned
bythe before trigger seemed fine. I then found that an additional UPDATE statement in the original 'before update'
triggerwas 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
triggerstarted 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
subsequenttriggers are not fired.' However, the NULL was returned by an AFTER trigger, and not a BEFORE trigger, but
itwas within the cascade of events generated by the BEFORE trigger. This is Postgres 8.3.9 (I know, but upgrading
wouldbe 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
unreasonablethat 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
causedthe update to fail just a coincidence, and I should do further debugging?
Thanks,
Susan
