触发器

表A 客户信息(cifoid 客户编号 cifoname 客户名称 等等)

 表B 项目信息(itemid 项目编号 itCifoid 对应表A的客户编号)

 表C 跟单信息(orderid 跟单编号 oritemid 对应表B的项目编号)

 表D 合同信息(itpaid 合同编号 ititemid 对应表B的项目编号)

求一个触发器,在删除表A的时候同时能删除表B,表C,表D相应的数据

 

 

create trigger trigger_delete_a
on A
for delete
as
set nocount on
delete D from D join B on D.ititemid=B.itemid join DELETED t on B.itCifoid=t.cifoid
delete C from C join B on C.oritemid=B.itemid join DELETED t on B.itCifoid=t.cifoid
delete B from B join deleted t on B.itCifoid=t.cifoid
set nocount off
go

 

create table cc
(
bid int identity(1,1),
nming nvarchar(30),
btel char(10)
)
insert into cc(nming,btel)values('张三','a')
insert into cc(nming,btel)values('王一','b')
insert into cc(nming,btel)values('李二','c')
insert into cc(nming,btel)values('张民','d')
insert into cc(nming,btel)values('张书','e')

select  * from aa
select * from bb
select * from cc

create trigger tabc
on aa
FOR delete
as
declare @nming nvarchar(300)
set @nming=(select nming from deleted)
begin
--delete aa from aa where nming=@nming这句注销的目的是因为超出了存储过程、函数、触发器或视图的最大嵌套层数最大层数为 32..

delete bb from bb where nming=@nming
delete cc from cc where nming=@nming
end

 

 

delete from aa where nming='张三'