请问各位高手如何Delete一棵树

[tangyanjun1]

用SQL语句如何Delete一棵树
我的表是这样样的
CREATE TABLE T_Menu(
MenuID //菜单ID
ParentMenuID //父菜单ID
)
我想把一级菜单Delete掉，下级菜单也要Delete掉
请问各位高手如何Delete?

[pengdali]

declare @参数 int
set @参数=1


declare @tmp1 table (id int)

insert @tmp1 values (@参数)
while @@rowcount>0
insert @tmp1 select a.MenuID from T_Menu a,@tmp1 b where a.ParentMenuID=b.ID and a.MenuID not in (select ID from @tmp1)

delete T_Menu where MenuID in(select id from @tmp1)

[txlicenhe]

最好用触发器，递归

create trigger 触发器名 on t_Menu
For delete
As
delete T_Menu where ParentMenuID in (Select MenuID from deleted)
go

然后直接删除一级菜单即可。

[yoki]

create proc delTree (@ int)
as
declare @result varchar(1000)
set @result=cast(@ as varchar(10))
while exists (select 1 from T_Menu where ParentMenuID=@)
begin
select @result=@result+','+cast(MenuID as varchar(10)) ,@=MenuID
from T_Menu
where ParentMenuID=@
end
exec ('delete T_Menu where MenuID in (' +@result + ')')
go

[yoki]

调用:

exec delTree 2

删除MenuID为2的树

[yoki]

测试如下：
CREATE TABLE T_Menu(
MenuID int, --菜单ID
ParentMenuID int --父菜单ID
)
go
insert into T_Menu select 1,null
insert into T_Menu select 2,1
insert into T_Menu select 3,1
insert into T_Menu select 4,2
insert into T_Menu select 5,4

go

create proc delTree (@ int)
as
declare @result varchar(1000)
set @result=cast(@ as varchar(10))
while exists (select 1 from T_Menu where ParentMenuID=@)
begin
select @result=@result+','+cast(MenuID as varchar(10)) ,@=MenuID
from T_Menu
where ParentMenuID=@
end
exec ('delete T_Menu where MenuID in (' +@result + ')')
go

exec delTree 2
go
select * from t_menu

[Chiff]

补充：递归支持32级

[zjcxc]

--不用递归,用下面的方法处理
declare @id int
set @id=1 --你要删除的菜单ID

--下面是删除处理
declare @t table(id int,le int)
declare @le int
set @le=0
insert into @t select menuid,@le from t_menu where menuid=@id
while @@rowcount>0
begin
set @le=@le+1
insert into @t select a.menuid,@le from t_menu a join @t b on a.ParentMenuID=b.id
where b.le=@le-1
end

delete from t_menu where menuid in(select id from @t)

[tangyanjun1]

pengdali(大力 V3.0) 的方法是可行的。

测试结果如下：
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'T_Menu')
DROP table T_Menu
GO

CREATE TABLE T_Menu(
MenuID int, --菜单ID
ParentMenuID int --父菜单ID
)
go
insert into T_Menu select 1,1
insert into T_Menu select 2,1
insert into T_Menu select 3,1
insert into T_Menu select 4,2
insert into T_Menu select 5,4

create proc del(@参数 int)
as
declare @tmp1 table (id int)

insert @tmp1 values (@参数)
while @@rowcount>0
insert @tmp1 select a.MenuID from T_Menu a,@tmp1 b where a.ParentMenuID=b.ID and a.MenuID not in (select ID from @tmp1)

delete T_Menu where MenuID in(select id from @tmp1)
go

exec del 1

结果为：
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(5 row(s) affected)

yoki(小马哥) 的好像有点问题
测试如下：
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'T_Menu')
DROP table T_Menu
GO

CREATE TABLE T_Menu(
MenuID int, --菜单ID
ParentMenuID int --父菜单ID
)
go
insert into T_Menu select 1,1
insert into T_Menu select 2,1
insert into T_Menu select 3,1
insert into T_Menu select 4,2
insert into T_Menu select 5,4

go

create proc delTree (@ int)
as
declare @result varchar(1000)
set @result=cast(@ as varchar(10))
while exists (select 1 from T_Menu where ParentMenuID=@)
begin
select @result=@result+','+cast(MenuID as varchar(10)) ,@=MenuID
from T_Menu
where ParentMenuID=@
end
exec ('delete T_Menu where MenuID in (' +@result + ')')
go

exec delTree 1
go

结果为：
(3 row(s) affected)
select * from t_menu
MenuID ParentMenuID
4 2
4 5

呵呵，不好意思，yoki(小马哥)，感觉上你的proc有问题，好像只能删除两级的树，
不过应该改过后还是可以用的。
txlicenhe(马可)
create trigger 触发器名 on t_Menu
For delete
As
delete T_Menu where ParentMenuID in (Select MenuID from deleted)
go
好像应该可以，但是测试结果跌掉我的眼镜。
测试如下：
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'T_Menu')
DROP table T_Menu
GO

CREATE TABLE T_Menu(
MenuID int, --菜单ID
ParentMenuID int --父菜单ID
)
go
insert into T_Menu select 1,null
insert into T_Menu select 2,1
insert into T_Menu select 3,1
insert into T_Menu select 4,2
insert into T_Menu select 5,4

create trigger deltrigger on T_Menu
for delete
As
delete T_Menu where ParentMenuID in (Select MenuID from deleted)
go

delete from T_Menu where ParentMenuID = 1

结果如下：
(2 row(s) affected)

select * from t_menu
MenuID ParentMenuID
1 NULL
5 4
是不是我什么地方没设对阿？

我看了帮助，把
use master
exec sp_dboption 'ACCESSCONTROL','recursive triggers','true'
exec sp_configure 'nested triggers','1'
都设成true了。但是当我执行的时候，它竟然说：

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

真是晕啊。那位高手知道这个情况？

恕我SQL应用不深，如果说错了，请指教。

不过还是谢谢各位了。
我分不多，还要留着以后问问题，所以不好给各位再添分了。
谢谢各位！