日期:2014-05-18 浏览次数:20491 次
--1、建立一个触发器(推荐) create trigger on p for delete as delete from spj where pno = (select pno from deleted) go --执行删除 delete from p where pname='螺丝' --2、级联删除 alter table p add constraint pk_p_id primary key (pno) go --为tb创建外健,并指定级联删除 alter table spj add constraint fk_spj_aid foreign key (pno) references p(pno) on delete cascade go
------解决方案--------------------
建立测试数据
if object_id('dbo.SPJ') is not null drop table dbo.SPJ; go if object_id('dbo.P') is not null drop table dbo.P; go create table dbo.P ( pno int not null primary key, pname nvarchar(20) not null ); go create table dbo.SPJ ( sno int not null primary key, pno int not null ); insert into dbo.P select 1, 'type-a' union all select 2, 'type-b' union all select 3, 'type-c'; go insert into dbo.SPJ select 1, 1 union all select 2, 1 union all select 3, 1 union all select 4, 2 union all select 5, 3 union all select 6, 3; go
------解决方案--------------------
建议用外键约束
先删除子表在删除父表
------解决方案--------------------
?个人建议用事务处理。
begin tran trPDel delete from spj where pno = (select top 1 pno from p where pname='螺丝') delete from p where pname='螺丝' if @@error<>0 begin rollback tran trPDel return end commit tran trPDel