Florian Pflug <fgp(at)phlo(dot)org> wrote:
> To summarize, here's what I currently believe would be a sensible
> approach:
>
> After every BEFORE trigger invocation, if the trigger returned
> non-NULL, check if latest row version is still the same as when
> the trigger started. If not, complain.
That certainly has the advantages of being a small, safe change and
of being easy to explain. It would certainly prevent the
astonishing sorts of behaviors which now occur and which can leave
people with database contents they thought they had guards against.
The down side is that something this strict does make it hard to
achieve certain behaviors which could be desirable for maintaining
redundant data for performance. In my bottom-up delete scenario,
there would either need to be somewhere to note that a row was being
deleted so that the delete of the children could skip maintaining
it, or the cascade would need to be implemented in the AFTER
triggers, and validations would need to accept orphans which could
be created. Either approach can be made to work, but from the
application development side, it's not as clean or easy.
The suggested approach for UPDATE with my original approach to
DELETE would make me happier, but I'm still not clear on Robert's
use cases and how that would affect him. Can you clarify why you
feel UPDATE and DELETE should both do this?
-Kevin