On Wed, 22 Jun 2005, Tom Lane wrote: > 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.
Is there anything we have right now that will handle this kind of thing without requiring either updating all the counts after a deletion in a statement trigger or once per row updating all the counts for records with the same "a" (doing something like make a sequence and using it in a subselect matching keys)? ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
