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

[abcdtianzi]

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

[APHY]

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

[APHY]

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

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

[bala7229291]

建立测试数据

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

[yuanmeixiang]

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

[代码兔]

个人建议用事务处理。

　

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