create table A ( F_id int, F_name varchar(10), F_baseID int )insert A select 1,'a',0 union select 2,'b',1 union select 3,'c',2 go--测试 create function f_tb(@F_id int) returns @tb table(F_id int,F_baseID int) as begin insert @tb select F_id,F_baseID from A where F_id=@F_id while @@rowcount>0 begin insert @tb select A.F_id,A.F_baseID from A join @tb B on A.F_id=B.F_baseID where not exists(select 1 from @tb where F_id=A.F_id) end return end go--删除 declare @F_id int --要删除的F_id set @F_id=3delete from A where F_id in(select F_id from f_tb(@F_id))--查看 select * from A--删除测试环境 drop function f_tb drop table A --结果 /* F_id F_name F_baseID ----------- ---------- ----------- (所影响的行数为 0 行) */
create table t1(f_id int,f_name varchar(20),f_base int) insert t1 select 1,'a',0 union all select 2,'b',1 union all select 3,'c',2 gocreate trigger ti_t1 on t1 for delete as delete a from t1 a,deleted b where a.f_id=b.f_base --delete a from t1 a,deleted b where a.f_id=b.f_id go select * from t1 delete t1 where f_id=3 go
(
F_id int,
F_name varchar(10),
F_baseID int
)insert A
select 1,'a',0 union
select 2,'b',1 union
select 3,'c',2
go--测试
create function f_tb(@F_id int)
returns @tb table(F_id int,F_baseID int)
as
begin
insert @tb select F_id,F_baseID from A where F_id=@F_id
while @@rowcount>0
begin
insert @tb
select A.F_id,A.F_baseID
from A
join @tb B on A.F_id=B.F_baseID
where not exists(select 1 from @tb where F_id=A.F_id)
end return
end
go--删除
declare @F_id int --要删除的F_id
set @F_id=3delete from A where F_id in(select F_id from f_tb(@F_id))--查看
select * from A--删除测试环境
drop function f_tb
drop table A
--结果
/*
F_id F_name F_baseID
----------- ---------- ----------- (所影响的行数为 0 行)
*/
create table t1(f_id int,f_name varchar(20),f_base int)
insert t1
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2
gocreate trigger ti_t1 on t1
for delete
as
delete a from t1 a,deleted b where a.f_id=b.f_base
--delete a from t1 a,deleted b where a.f_id=b.f_id
go
select * from t1
delete t1 where f_id=3
go