请求SQL语句
表：
idnamepid
01张三00
02李四00
03王五01
04牛六02
05马七04
06张八03
如何写delete语句，删除id=‘01’，同时把‘01’的所有子孙节点完全删除
例如：我删除id=‘01’后，表记录为
表：
idnamepid
02李四00
04牛六02
05马七04
[解决办法]
--建立函數
Create Function F_GetChildren(@id Varchar(10))
Returns @Tree Table (id Varchar(10), pid Varchar(10))
As
Begin
Insert @Tree Select id, pid From 表 Where id = @id
While @@Rowcount > 0
Insert @Tree Select A.id, A.pid From 表 A Inner Join @Tree B On A.pid = B.id And A.id Not In (Select id From @Tree)
Return
End
GO
--測試
Delete From 表 Where ID In (Select id From dbo.F_GetChildren( '01 '))
Select * From 表
[解决办法]
--建立測試環境
Create Table 表
(id Varchar(10),
name Nvarchar(10),
pid Varchar(10)
)
Insert 表 Select '01 ',N '张三 ', '00 '
Union All Select '02 ',N '李四 ', '00 '
Union All Select '03 ',N '王五 ', '01 '
Union All Select '04 ',N '牛六 ', '02 '
Union All Select '05 ',N '马七 ', '04 '
Union All Select '06 ',N '张八 ', '03 '
GO
--建立函數
Create Function F_GetChildren(@id Varchar(10))
Returns @Tree Table (id Varchar(10), pid Varchar(10))
As
Begin
Insert @Tree Select id, pid From 表 Where id = @id
While @@Rowcount > 0
Insert @Tree Select A.id, A.pid From 表 A Inner Join @Tree B On A.pid = B.id And A.id Not In (Select id From @Tree)
Return
End
GO
--測試
Delete From 表 Where ID In (Select id From dbo.F_GetChildren( '01 '))
Select * From 表
GO
--刪除測試環境
Drop Table 表
Drop Function F_GetChildren
--結果
/*
idnamepid
02李四00
04牛六02
05马七04
*/
[解决办法]
--经过测试，触发器可以实现，代码如下
if exists(select name from sysobjects where name= 'test 'and type= 'U ')
drop table [dbo].[test]
go
create table [dbo].[test](id char(10),name char(20),pid char(10))
go
insert into test
select '00 ', 'aaa ', '00 '
union all select '01 ', 'aaa ', '00 '
union all select '02 ', 'aaa ', '00 '
union all select '03 ', 'aaa ', '01 '
union all select '04 ', 'aaa ', '01 '
union all select '05 ', 'aaa ', '02 '
union all select '06 ', 'aaa ', '02 '
union all select '07 ', 'aaa ', '04 '
union all select '08 ', 'aaa ', '04 '
union all select '09 ', 'aaa ', '05 '
union all select '10 ', 'aaa ', '05 '
go
if exists(select name from sysobjects where name= 'tg_test 'and type= 'TR ')
drop trigger [dbo].[tg_test]
go
create trigger [dbo].[tg_test]
on [dbo].[test]
after delete
as
begin
if exists(select 1 from test where pid in(select id from deleted ))
delete from test where pid in(select id from deleted )
end
go
select*from [dbo].[test]
delete from [dbo].[test] where id= '02 '
select*from [dbo].[test]
/*结果
id name pid
---------- -------------------- ----------
00 aaa 00
01 aaa 00
03 aaa 01
04 aaa 01
07 aaa 04
08 aaa 04
09 aaa 05
10 aaa 05
*/
[解决办法]
--要设置允许递归触发才行:
alter database 你的库名 set RECURSIVE_TRIGGERS ON
RECURSIVE_TRIGGERS ON | OFF
如果指定为 ON，将允许递归激发触发器。RECURSIVE_TRIGGERS OFF（默认值）只禁止直接递归。若要也禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。
