Trigger Dilemma

From: <kal121_at_my-deja.com>
Date: 2000/04/10
Message-ID: <8ct7er$gou$1@nnrp1.deja.com>#1/1

Hello,

Here's the problem:

I have a parent and child table. The child table has a foreign key that references the parent's primary key.

I'd like to write a trigger that, when a parent row is deleted, the corresponding child row will be UPDATED with some default value in one of the columns to indicate that it is an "orphan".

Problem is, I can't delete a row from the parent table due to the foreign key on the child table. Nor can I declare ON CASCADE DELETE, because the child row will be deleted instead of being updated by the trigger.

The only solution I can see is dropping the foreign key altogether, but I'd rather not do this. Is there another way???

Thanks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CDT