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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
