Cascade Delete

[David Satz]

SQL Server 2000 supports cascading deletes, but not previous versions
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"ss" <smintell@yahoo.com> wrote in message news:3cb5d02a$1@10.1.10.29...
>
> When i delete a record in one table is there a way that i can delete the
relating
> records in the other table automatically?
> I have already written a stored procedure but instead of this is there any
> other way to do this?
> thanks in advance

[ss]

When i delete a record in one table is there a way that i can delete the relating
records in the other table automatically?
I have already written a stored procedure but instead of this is there any
other way to do this?
thanks in advance

[Narayana Pakala]

Hi

You have to write a trigger for delete on the independent table
so that when a row is deleted the trigger code deletes one/more rows
in the dependent table. Check this example where Title_Author table depends
on Authors table and a delete from authors cascades delete into
Title_Author table.
-------------------------------------------------
CREATE TRIGGER t_d_Authors
ON Authors
FOR DELETE
AS
SET NOCOUNT ON

DELETE TitleAuthor FROM deleted, Title_Author
WHERE deleted.Au_ID = Title_Author.Au_ID
-------------------------------------------------

Regards
Narayana

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>SQL Server 2000 supports cascading deletes, but not previous versions
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"ss" <smintell@yahoo.com> wrote in message news:3cb5d02a$1@10.1.10.29...
>>
>> When i delete a record in one table is there a way that i can delete the
>relating
>> records in the other table automatically?
>> I have already written a stored procedure but instead of this is there
any
>> other way to do this?
>> thanks in advance
>
>

[David Satz]

Since constraints are checked before triggers, you would not be able do that
if for example, Au_ID was a foreign key to Authors (as it s/b). I would
then create a delete SP as "ss" did.

Dave
"Narayana Pakala" <narayanap@hotmail.com> wrote in message
news:3cbd42a3$1@10.1.10.29...
>
> Hi
>
> You have to write a trigger for delete on the independent table
> so that when a row is deleted the trigger code deletes one/more rows
> in the dependent table. Check this example where Title_Author table
depends
> on Authors table and a delete from authors cascades delete into
> Title_Author table.
> -------------------------------------------------
> CREATE TRIGGER t_d_Authors
> ON Authors
> FOR DELETE
> AS
> SET NOCOUNT ON
>
> DELETE TitleAuthor FROM deleted, Title_Author
> WHERE deleted.Au_ID = Title_Author.Au_ID
> -------------------------------------------------
>
> Regards
> Narayana
>
> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >SQL Server 2000 supports cascading deletes, but not previous versions
> >--
> >HTH,
> >David Satz
> >Principal Web Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >"ss" <smintell@yahoo.com> wrote in message news:3cb5d02a$1@10.1.10.29...
> >>
> >> When i delete a record in one table is there a way that i can delete
the
> >relating
> >> records in the other table automatically?
> >> I have already written a stored procedure but instead of this is there
> any
> >> other way to do this?
> >> thanks in advance
> >
> >
>

[Piyush]

Dave,
If you use 'on delete cascade' option on the child table and specify it for
the foreign key, then I think it should work. heres an example.

Dept (deptID, blah blah...)
Emp (EmpId, deptID, blah, blah..)

write the following statement

alter table emp constraint constraint_name foreign key (deptID) references
dept(deptID) on delete cascade;

it should work
Also if you are defining this constraint within the emp table then you can
skip "foreign key (deptID)" from the above statement. The attribute (deptID)
is also optional. Sothe above statement would become

deptId data_type references dept on delete cascade;

I would suggest use the alter statement and name the constraint to avoid
alot of chicken and egg questions.

Piyush




"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>Since constraints are checked before triggers, you would not be able do
that
>if for example, Au_ID was a foreign key to Authors (as it s/b). I would
>then create a delete SP as "ss" did.
>
>Dave
>"Narayana Pakala" <narayanap@hotmail.com> wrote in message
>news:3cbd42a3$1@10.1.10.29...
>>
>> Hi
>>
>> You have to write a trigger for delete on the independent table
>> so that when a row is deleted the trigger code deletes one/more rows
>> in the dependent table. Check this example where Title_Author table
>depends
>> on Authors table and a delete from authors cascades delete into
>> Title_Author table.
>> -------------------------------------------------
>> CREATE TRIGGER t_d_Authors
>> ON Authors
>> FOR DELETE
>> AS
>> SET NOCOUNT ON
>>
>> DELETE TitleAuthor FROM deleted, Title_Author
>> WHERE deleted.Au_ID = Title_Author.Au_ID
>> -------------------------------------------------
>>
>> Regards
>> Narayana
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >SQL Server 2000 supports cascading deletes, but not previous versions
>> >--
>> >HTH,
>> >David Satz
>> >Principal Web Engineer
>> >Hyperion Solutions
>> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >(Please reply to group only - emails answered rarely)
>> >-----------------------------------------------------------------
>> >"ss" <smintell@yahoo.com> wrote in message news:3cb5d02a$1@10.1.10.29...
>> >>
>> >> When i delete a record in one table is there a way that i can delete
>the
>> >relating
>> >> records in the other table automatically?
>> >> I have already written a stored procedure but instead of this is there
>> any
>> >> other way to do this?
>> >> thanks in advance
>> >
>> >
>>
>
>