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.

Hmmm... I'm getting confused here --

"referenced table" -> "parent"
"referencing table" -> "child"

right?

Then, I expect the same as you.

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

Exactly when, and why?

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

Right.

With regards,

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