SQL sever中要删除两个相关联的表该如何进行级联删除

如题，举个例子，现有P表（pno，pname）,SPJ表(sno,pno)，现在要删除p表中pname为螺丝的元组记录，顺便把在spj表相应的记录删除，而delete又不能同时删两个表，那该如何进行级联删除？

5 个解决方案

#1

--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

#2

--推荐触发器控制，可控性比较强

--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

#3

建立测试数据



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

这种不外乎两种办法嘛
第一，为P表建立触发器，即：当删除P表的记录时，先将SPJ表里的pno相同的记录全部删除掉。



--建立触发器

create trigger tr_P_DIN

	on dbo.P

	instead of delete

as

begin

	delete dbo.SPJ

	from dbo.SPJ s

		inner join deleted d

			on s.pno = d.pno;


	delete dbo.P

	from dbo.P p

		inner join deleted d

			on p.pno = d.pno;

end;

go


--测试级联效果

delete dbo.P

where pno = 1;


select *

from dbo.SPJ;

/*

执行结果

sno         pno

----------- -----------

4           2

5           3

6           3

*/

2.另外一种方法是让SPJ表引用P表，形成外键关系，然后使之级联删除



--创建外键约束

--把上面创建的触发器删除

alter table dbo.SPJ

	add constraint FK_SPJ_P foreign key(pno) references dbo.P(pno)

		on delete cascade;


--测试删除级联

delete dbo.P

where pno = 1;


select *

from dbo.SPJ;

/*

测试结果

sno         pno

----------- -----------

4           2

5           3

6           3

*/

#4

建议用外键约束
先删除子表在删除父表

#5

个人建议用事务处理。

　



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

#1

#2