Hi Ann,

Thanks for the info, this explains everything. Still, this behaviour
seems unnatural to me, wouldn't it be more logical to delete the child
records before deleting the parent one, i.e. avoiding the inconsistent
state where we have a child with parent id, with no such parent?

I have not tested this with other DB's, is Firebird behaviour in this
case consistant with that of other databases? Is there any plan to
change this behaviour in future releases?

Thanks,

Valentin Samko
http://val.samko.info

Monday, December 6, 2004, 4:13:40 PM, you wrote:


AWH> At 11:00 AM 12/5/2004, Valentin Samko wrote:

>>I have found a bug in my program where a few database tables are not
>>updated properly due to Firebird unable to access certain data from
>>the trigger.

AWH> A foreign key with "on delete cascade" causes the delete of the
AWH> parent before it begins deleting related records from the child
AWH> table. A before delete record in the child executes after the
AWH> parent is gone.

>>I do not know whether this is the expected behaviour or not, and if it
>>is configurable (I really hope so).

AWH> Yes, it's expected and no, it's not configurable, directly. You
AWH> could change your implementation so a before delete trigger in the
AWH> parent table deletes the related records in the child table. In
AWH> general, such triggers are less reliable than equivalent constraints
AWH> but in this case the triggers have two advantages. First, they do
AWH> what you want. Second, you can do both - define both a before
AWH> delete trigger on the parent table and an "on delete cascade" foreign
AWH> key relationship between the tables.



AWH> Regards,


AWH> Ann