Hello Ann,

As you only posted "answer found"... I have a question
though...

> 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.

Isn't "post_<action>" the right behaviour for a system trigger?

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

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

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com