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

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

[解决办法]
--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
[解决办法]

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

SQL code

--1、建立一个触发器(推荐)create trigger on p for deleteas   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 cascadego
[解决办法]
建立测试数据
SQL code
if object_id('dbo.SPJ') is not null    drop table dbo.SPJ;goif object_id('dbo.P') is not null    drop table dbo.P;gocreate table dbo.P(    pno int not null primary key,    pname nvarchar(20) not null);gocreate table dbo.SPJ(    sno int not null primary key,    pno int not null);insert into dbo.Pselect 1, 'type-a' union allselect 2, 'type-b' union allselect 3, 'type-c';goinsert into dbo.SPJselect 1, 1 union allselect 2, 1 union allselect 3, 1 union allselect 4, 2 union allselect 5, 3 union allselect 6, 3;go
[解决办法]
建议用外键约束
先删除子表在删除父表
[解决办法]

 ?个人建议用事务处理。

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