Subject Re: [Firebird-Architect] Order of check triggers for foreign keys
Author Ann W. Harrison
At 03:19 PM 12/8/2004, Martijn Tonies wrote:

>Hello Ann,
>As you only posted "answer found"...
>Isn't "post_<action>" the right behaviour for a system trigger?

If I were thinking about the case of foreign keys
abstractly, I'd expect the constraint actions to
happen on the referencing table before the primary
action happens on the referenced table. That way
the relationship between the two tables remains
consistent, even during the execution of the "verb"
that changes the referenced table.

>I haven't followed the thread in support, but what you describe
>seems to be working, or am I totally wrong there?

It's working - that is, at the end of the delete of the
record in the referenced table, everything is consistent
again. There is a point in the middle of the verb when
referencing records can notice that they're orphaned.
That's not great.

>What should the "before action" used-defined trigger do?

Delete (or modify) the referencing records so there is
no point at which they're orphaned. However, someone
quoted a message from Ded saying that leaving the
referenced records around while deleting the referencing
records introduces the risk that someone will store a
new referencing record in the middle of the operation
which wouldn't be noticed and would be orphaned when the
referenced record was finally deleted. In theory,
running the trigger in system context would keep that
from happening - just as it keeps people from storing
duplicate values in indexes - but there's sometimes a
gulf between theory and practice.