Cascade Delete

Hi,
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer