Just because it's traditional, I feel I have to chime in with information on
how Informix deals with cascading deletes. (Because Oracle isn't the only
option for non-Sheep)
Informix triggers include both the 'old' and 'new' row information, so you
can build cascading deletes using triggers if you want to -- as you would in
Interbase (and Oracle?)
Put you can also do the following:
create table product ( product_no int, description varchar(30), primary key
(product_no));
create table product_colours ( product_no int, colour varchar(30),
foreign key (product_no)
references product ON DELETE CASCADE);
Now whenever a row is deleted from the 'product' table, it gets removed from
'product_colours' too.
And so, now you know :-)
Kerry S
----- Original Message -----
From: Nic Wise <[EMAIL PROTECTED]>
To: Multiple recipients of list database <[EMAIL PROTECTED]>
Sent: Monday, November 08, 1999 4:35 PM
Subject: Re: [DUG-DB]: Cascade deletes using Triggers in SQL7 V Interbase
> Short answer: you can't. Its an inherant flaw in MS/Sybase SQL Server.
> And most others, too - No before + after triggers, only after triggers
>
> Long answer:
>
> begin transaction
> --get the PK
> delete from childtable where K = thePK
>
> delete from parent where PK = thePK
>
> commit transaction
>
> yes, it IS that painfull.
>
> N
>
> Tony Blomfield wrote:
> >
> > HI.
> >
> > With Interbase it is a breeze to do cascade deletes using triggers
because
> > of IB's concept of New and Old.
> >
> > With SQL7 it seems nobody thought of this, because the delete is
attempted
> > BEFORE the trigger is called.
> >
> > Consider: T1 is parent, T2 is Child with FK constraint to T1
> > If I write a simple trigger. The PK for T1 is called T1ID
> >
> > create trigger DelT1 on T1
> > for delete as
> > declare @T1ID int
> > begin
> > /* now casacade */
> >
> > select @T1ID = T1IDSecurityGroupID from Deleted;
> > delete from T2 where T2ID = @T1ID;
> > end
> >
> > When I execute Delete from T1 where T1ID = 3
> >
> > I allways get the FK Constraint Violation, and my delete trigger is not
> > called.
> >
> > QUESTION
> >
> > How do I get a delete trigger to run before SQL7 actually does the
delete,
> > or How do others handle cascade deletes in SQL7?
> >
> > TIA,
> >
> > Tony.
> >
>
> --------------------------------------------------------------------------
-
> > New Zealand Delphi Users group - Database List -
[EMAIL PROTECTED]
> > Website: http://www.delphi.org.nz
>
> --
> Nic Wise - Inprise New Zealand / Brocker Technologies Web Monkey.
> mob:+64.21.676.418 - wk:+64.9.481.9999 x9753 - wk-em:[EMAIL PROTECTED]
> hm: +64.9.277.5309 - hm-em:[EMAIL PROTECTED]
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
