Stephan Szabo <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])