Subject Re: [firebird-support] on delete cascade
Author Ann W. Harrison
lobolo2000 wrote:
> Shouldn't FB2 delete master records after detail records if an 'on
> delete cascade' clause is used (otherwise the referential integrity
> would be violated)?

It doesn't matter. The delete of the master and the cascading
deletes of the children are a single operation and succeed or
fail together. They can be executed in either order, since the
sequence of the operation should not be visible outside of
the operation.

You have, of course, found an exception .... a trigger on the
delete of the child will notice the order and raise a spurious
error. That's an artifact of the conflict between Firebird's
row by row approach to operational consistency and the SQL
standard which treats each verb as an atomic operation.
Theoretically (I guess) the before delete trigger for the
child should fire before any part of the delete that cascades
to the child, and after delete triggers on the master and the
child should happen after the children are deleted.

But they don't and changing that behavior will be a major