Hello all,

Someone on the support list created a before delete
trigger on a table that is the referencing table in a
foreign key constraint that specifies ON DELETE CASCADE.
The trigger checks for the existence of a matching row
in the referenced table and logs either "found" or "not
found". The writer was unhappy that the delete trigger
reports that the referenced record was "not found."

ON DELETE CASCADE - like all actions instigated by
foreign key constraints - is implemented with a post
operation system trigger on the referenced table. If
the writer had a before update trigger on the referencing
table in a SET NULL or SET DEFAULT action, it would show
the same behavior. The referenced record is updated
or deleted before changes are made to the referencing
record(s).

So there is a moment which can be detected only by
triggers on the referencing table, when the foreign
key constraint appears not to be enforced. The referenced
record is gone or modified and the referencing records
are still around. It's tiny, transient window, but not
it's there.

To close that window, we could change ddl.c to declare
the trigger types of the system triggers for foreign key
operations as PRE_MODIFY and PRE_ERASE rather than
POST_MODIFY and POST_ERASE.

Should we? What else would that break? As far as I
can tell, the standard says that the behavior is
implementation defined.


Regards,


Ann