Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
>> PROCEDURE resort_test1();
> I think this will work in an after delete trigger, but not in a before
> delete trigger (and seems to in my tests). I'm not sure what the spec says
> about the visibility of rows in cases like this.
Well, the actual effect is that the first trigger's UPDATE changes all
the rows that the DELETE might later delete, thus overriding the delete.
(A query cannot modify rows already modified by commands started later
in the same transaction, such as commands issued by triggers fired by
the query itself.)
Depending on the order that the DELETE hits the rows in, there might be
more than one row that can get processed before the UPDATEs have touched
all remaining rows, so this is all pretty messy and not to be relied on.
I suspect that if you read the spec carefully it would want a "triggered
data change violation" error raised here. My advice is not to use a
BEFORE trigger for this.
Even an AFTER trigger will have some pretty significant problems with
this, I'm afraid, because of the uncertainty about the order in which
the rows are deleted (and hence the order in which the trigger instances
fire). For instance, suppose you delete the rows with c=1 and c=2, and
they get visited in that order. The UPDATE for c=1 will update the row
currently having c=3 to c=2 ... whereupon that row will NOT be seen as
an update candidate by the UPDATE for c=2. (You could work around that
case by using ">= OLD.c" instead of "> OLD.c", but it could still fail
with more than 2 rows being deleted.) The proposed trigger only works
cleanly if the rows are deleted in decreasing order of c, and there's no
very easy way to guarantee that.
regards, tom lane