Wednesday, December 8, 2004, 7:01:17 PM, you wrote:

AWH> At 09:17 AM 12/7/2004, Valentin Samko wrote:

AWH> NOTE 398 — “Marking for deletion” is an
AWH> implementation-dependent mechanism.

AWH> So, yes, there is a temporary state during the execution of a
AWH> foreign key constraint - ON UPDATE or ON DELETE - when the parent
AWH> is changed and the child is not. That condition can be detected
AWH> by triggers on the child. As far as I understand the standard,
AWH> our behavior is legitimate, if weird.

I believe, there are some reasons for this behaviour, but it just
seems illogical to me. All my code (stored procedures, etc)
assume that they always can obtain information about child's parent
by doing select a, b, c from parent where id = child.parent_id. Now it
appears that my assumption was wrong, although I thought that having
on ... cascade constraint I could have such an assumption.

AWH> Out of curiosity, why is this important to you?
It's not that I can't live without this requirement, it was just quite
an unpleasant surprise. I could not figure out for quite some time,
why some values were not updated properly when my client deleted
sales.

Now I fixed this issue by having "before delete" triggers on all the
"parent" tables, deleting child records straight from there. Hopefully
I will remember about this "feature" each time I add a new
parent->child relationship :)

Having on_delete triggers having an access to parent records is
extremely important in my software because each time one deletes a
sale/purchase/stock title/invoice/delivery/... I need to correctly
update all the quantities, etc. and add recovery (to be able to roll
these changes back) and audit entries to other tables.

Thanks,

Valentin Samko
http://val.samko.info