Re: Trigger Dilemma

Yes, delete set null preserves the child record while deleting the parent record. However, it would still be nice to set the foreign key column to some default value. Is there a way to do this without removing the foreign key constraint?

In article <955394207.10318.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> Upgrade to Oracle 8i, has on delete nullify.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <kal121_at_my-deja.com> schreef in berichtnieuws
 8ct7er$gou$1_at_nnrp1.deja.com...
> > 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.
>
>

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