create trigger trg_delete
on A
for delete
as
delete B from B,deleted where B.Aid=deleted.BaseID
go
on A
for delete
as
delete B from B,deleted where B.Aid=deleted.BaseID
go
解决方案 »
- 菜鸟求问SQLServer 安装成功但失败
- 请问ALTER DATABASE [DB] ADD FILEGROUP 可以使用变量吗?
- update触发器这样写,不行!只是将修改的数量直接增加到库存数量上去,而不是去修改数量
- 关于in 后面的表达式有长度限制吗
- 怎么在操作游标时使用字符串变量?
- 问题出在哪里,哪位帮我找找看?
- .net 评论表获取前10个最新的评论内容(用户不同)
- 什么叫虚拟库存,是不是虚拟库存的定义可以由企业自行定义,不是很了解这方面的?
- 如何删除主键?
- 帮帮看一下这个存储过程啊,为什么top 处老出错呢?,急,高分送出
- 如何能找到所有包含中文的表
- 怎样把要置顶的信息和其它信息一起查询出来
returns @tb table(Aid int)
as
begin
insert @tb select Aid from A where Aid=@Aid
while @@rowcount>0
begin
insert @tb
select A.Aid
from A
join @tb B on A.BaseID=B.Aid
where not exists(select 1 from @tb where Aid=A.Aid)
end return
end
go--删除
declare @Aid int --要删除的Aid
set @Aid=1delete from A where Aid in(select Aid from f_tb(@Aid))
delete from B where Aid in(select Aid from f_tb(@Aid))
(
Aid int,
name varchar(10),
BaseID int
)
create table B
(
Bid int,
name varchar(10),
AID int
)
insert A
select 1,'张三',0 union
select 2,'李较',1 union
select 3,'王孙',2
insert B
select 1,'李城隍',3 union
select 2,'黄莺',1
go--测试
create function f_tb(@Aid int)
returns @tb table(Aid int)
as
begin
insert @tb select Aid from A where Aid=@Aid
while @@rowcount>0
begin
insert @tb
select A.Aid
from A
join @tb B on A.BaseID=B.Aid
where not exists(select 1 from @tb where Aid=A.Aid)
end return
end
go--删除
declare @Aid int --要删除的Aid
set @Aid=1delete from B where Aid in(select Aid from f_tb(@Aid))
delete from A where Aid in(select Aid from f_tb(@Aid))--查看
select * from A
select * from B--删除测试环境
drop function f_tb
drop table A,B
--结果
/*
Aid name BaseID
----------- ---------- ----------- (所影响的行数为 0 行)Bid name AID
----------- ---------- ----------- (所影响的行数为 0 行)
*/
create function f_getchild(@AID int)
returns @t table(AID INT,BaseID INT,Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select Aid,BaseID,@i from A where AID=@AID
while @@rowcount <> 0
begin
set @i = @i + 1
insert into @t
select
A.Aid,A.BaseID,@i
from
A,@t t
where
A.Aid=t.BaseID and t.Level=@i-1
end
return
end
go--创建存储过程
create procedure sp_deldata(@AID int)
as
begin
delete B from B,dbo.f_getchild(@AID) t where B.Aid = t.AID
delete A from A,dbo.f_getchild(@AID) t where A.Aid = t.AID
end
go