--处理的存储过程
create proc p_delete
@MenuID varchar(20) --要删除的MenuID
as
declare @pid varchar(20),@i int
select @i=len(@MenuID)-1,@pid=left(@MenuID,@i-1)+'%'
delete from 表 where MenuID=@MenuID
update 表 set MenuID=stuff(MenuID,@i,2
,right(99+cast(substring(MenuID,@i,2) as int),2))
where MenuID like @pid and MenuID>@MenuID
go--调用
exec p_delete '0201'
go
create proc p_delete
@MenuID varchar(20) --要删除的MenuID
as
declare @pid varchar(20),@i int
select @i=len(@MenuID)-1,@pid=left(@MenuID,@i-1)+'%'
delete from 表 where MenuID=@MenuID
update 表 set MenuID=stuff(MenuID,@i,2
,right(99+cast(substring(MenuID,@i,2) as int),2))
where MenuID like @pid and MenuID>@MenuID
go--调用
exec p_delete '0201'
go
解决方案 »
- com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 无效
- 急求 sql语句
- sql server agent 无法启动
- 突发奇想,讨论一个问题
- txt文档导入SqlServer,使用存储过程?
- 全文检索填充问题?!!急............
- 急!!!请教SQL语句
- 请教:为什么我的数据库只能读不能写?
- 这里需要你的经验,50分
- 谁想拉 "Microsoft SQL Server 2000 简体中文版 4 In 1",请进! (内详)
- 我创建一个用户时,需要给他授权,也就是说这个用户可能只能访问一张表中,
- 请问MSDE最大并发用户数多少?知道的回答,不要瞎猜,对的有分。
insert into 表
select '01','梨'
union all select '02','苹果'
union all select '0201','苹果大儿子'
union all select '0202','苹果二儿子'
union all select '020201','苹果二儿子的儿子'
union all select '0203','苹果三儿子'
union all select '03','香蕉'
go--处理的存储过程
create proc p_delete
@MenuID varchar(20) --要删除的MenuID
as
declare @pid varchar(20),@i int
select @i=len(@MenuID)-1,@pid=left(@MenuID,@i-1)+'%'
delete from 表 where MenuID=@MenuID
update 表 set MenuID=stuff(MenuID,@i,2
,right(99+cast(substring(MenuID,@i,2) as int),2))
where MenuID like @pid and MenuID>@MenuID
go--调用
exec p_delete '0201'
go--显示处理结果
select * from 表
go--删除测试
drop table 表
drop proc p_delete
go/*--测试结果
MenuID MenuName
-------------------- --------------------
01 梨
02 苹果
0201 苹果二儿子
020101 苹果二儿子的儿子
0202 苹果三儿子
03 香蕉(所影响的行数为 6 行)
--*/
--创建自动处理的触发器
create trigger t_delete on 表
for delete
as
update 表 set MenuID
=stuff(a.MenuID,b.level,2
,right(99+cast(substring(a.MenuID,b.level,2) as int),2))
from 表 a join(
select MenuID=min(MenuID),level=len(MenuID)-1
from deleted group by len(MenuID)-1
) b on a.MenuID like left(b.MenuID,len(b.MenuID)-2)+'%' and a.MenuID>b.MenuID
go
insert into 表
select '01','梨'
union all select '02','苹果'
union all select '0201','苹果大儿子'
union all select '0202','苹果二儿子'
union all select '020201','苹果二儿子的儿子'
union all select '0203','苹果三儿子'
union all select '03','香蕉'
go--创建自动处理的触发器
create trigger t_delete on 表
for delete
as
update 表 set MenuID
=stuff(a.MenuID,b.level,2
,right(99+cast(substring(a.MenuID,b.level,2) as int),2))
from 表 a join(
select MenuID=min(MenuID),level=len(MenuID)-1
from deleted group by len(MenuID)-1
) b on a.MenuID like left(b.MenuID,len(b.MenuID)-2)+'%' and a.MenuID>b.MenuID
go--删除测试
delete from 表 where MenuID='0201'
go--显示处理结果
select * from 表
go--删除测试
drop table 表
drop proc p_delete
go/*--测试结果
MenuID MenuName
-------------------- --------------------
01 梨
02 苹果
0201 苹果二儿子
020101 苹果二儿子的儿子
0202 苹果三儿子
03 香蕉(所影响的行数为 6 行)
--*/