触发器中删除一记录并删除子孙类
表结构如下
A(id,parentid,name)
1 0 xx
2 1 xx
3 2 xx
4 1 xx
如删除第一条记录，这里应该就没有数据了(parentid=id)
5 个解决方案
#1
exec sp_configure 'nested triggers',1
go
exec sp_dboption '库名','recursive triggers',true
go
CREATE TABLE gs(a int,p int)
insert gs select 1 ,0
union all select 2, 1
union all select 3, 2
union all select 4, 1
go
CREATE TABLE ll(a INT)
INSERT ll SELECT 0
go
CREATE TRIGGER t_gs
ON gs
FOR DELETE
AS
BEGIN
UPDATE ll SET a=a+1
IF EXISTS(SELECT 1 FROM ll WHERE a <32)
DELETE FROM gs WHERE EXISTS(SELECT 1 FROM DELETED WHERE gs.p=a)
END
go
DELETE FROM gs WHERE a=1
#2
--删除节点
--建立测试环境
IF OBJECT_ID('GoodType') IS NOT NULL DROP TABLE GoodType
GO
CREATE TABLE GoodType
(
id int ,
name varchar(20),
pid int,
tree int
)
GO
INSERT GoodType
select '1','a','0','0'
union all select '2','aa','1','1'
union all select '3','ab','1','1'
union all select '4','aaa','2','2'
union all select '5','aba','3','2'
union all select '6','abaa','5','3'
union all select '7','abab','5','3'
go
create trigger trd_GoodType on GoodType instead of delete
as
begin
update t set t.pid=d.pid ,t.tree=t.tree-1
from GoodType t,deleted d
where t.pid=d.id
delete GoodType where id in( select id from deleted)
end
go
create trigger tru_GoodType on GoodType for update
as
begin
update t set t.tree=t.tree-1
from GoodType t,inserted i,deleted d
where t.pid=i.id and t.pid=d.id and i.tree=d.tree-1
end
go
--查询
delete GoodType where id=3
select * from GoodType
go
--结果
/*
(7 行受影响)
(2 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
id name pid tree
----------- -------------------- ----------- -----------
1 a 0 0
2 aa 1 1
4 aaa 2 2
5 aba 1 1
6 abaa 5 2
7 abab 5 2
(6 行受影响)
*/
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx
#3
哎，搞错了
#4
if OBJECT_ID('tb') is not null
drop table tb;
go
create table tb (id int,pid int,name varchar(20) default 'Tom');
go
insert into tb (id,pid)
select 1,0 union all select 2,1 union all
select 3,2 union all select 4,1;
go
create trigger trg_tb_delete on tb
instead of delete
as
with t as(
select id from deleted
union all
select tb.id from t,tb where tb.pid=t.id
)
delete from tb where id in (select id from t);
go
begin tran
delete from tb where id=1;
select * from tb;
rollback tran
#5
4楼正解
智能推荐
注意！
本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。