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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
