我想在程序中实现编号自动生成，在数据中间插入的时候，后面的数据编号自动改变...

我想在程序中实现编号自动生成，在数据中间插入的时候，后面的数据编号自动改变
例如1.1，后面是1.11，1.12，我在中间插入1.11，后面的1.11，1.12自动变成1.12，1.13。
如果1.1后面是1.2，1.3，那就自动变成1.3，1.4。
谁有办法？

---

原数据：
1 
2
2.1
2.1.1 
2.2 
3 
3.1
插入 2
变成
原         现
1         1
插入        2
2          3
2.1       3.1
2.1.1     3.1.1
2.2       3.2
3          4
3.1        4.1
对否？

---

create function dbo.addid( @addid varchar ( 30 ), @id varchar ( 30 ))
returns varchar ( 30 )
as
begin
declare @newid varchar ( 30 ), @mid int
-- @addid = 1.2.1 @id = 1.2.2.1
if @addid >= @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex ( ' . ' , @addid ) > 0 set @addid = left ( @addid , len ( @addid ) + 1 - charindex ( ' . ' , reverse ( @addid )))
-- [1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid 
select @newid = @addid + ' . ' , @id = stuff ( @id , 1 , len ( @addid ) + 1 , '' )
-- @id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex ( ' . ' , @id ) > 1
begin
  set @mid = left ( @id , charindex ( ' . ' , @id ) - 1 )
  set @id = stuff ( @id , 1 , charindex ( ' . ' , @id ) - 1 )
end
else
begin
  set @mid = @id
  set @id = ''
end
-- [@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid + rtrim ( @mid + 1 ) + @id
return @newid
end
go
-- 例子，可以将UPDATE 语句放入表的INSERT 触发器。
declare @id varchar ( 30 )
insert tb(id) select   @id
update tb set id = dbo.addid( @id ,id) where id > @id

---

create table tb(id varchar ( 30 ))
insert tb select ' 1 '
union all select ' 1.1 '
union all select ' 2 '
union all select ' 2.1 '
union all select ' 2.1.2 '
union all select ' 2.2 '
union all select ' 3 '
union all select ' 3.1 '
go
create function dbo.addid( @addid varchar ( 30 ), @id varchar ( 30 ))
returns varchar ( 30 )
as
begin
declare @newid varchar ( 30 ), @mid int
-- @addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex ( ' . ' , @addid ) > 0 set @addid = left ( @addid , len ( @addid ) + 1 - charindex ( ' . ' , reverse ( @addid )))
-- [1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid 
-- 前缀不同的直接返回
if @addid <> left ( @id , len ( @addid )) return @id
select @newid = @addid , @id = stuff ( @id , 1 , len ( @addid ), '' )
-- @id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex ( ' . ' , @id ) > 1
begin
  set @mid = left ( @id , charindex ( ' . ' , @id ) - 1 )
  set @id = stuff ( @id , 1 , charindex ( ' . ' , @id ) - 1 , '' )
end
else
begin
  set @mid = @id
  set @id = ''
end
-- [@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid + rtrim ( @mid + 1 ) + @id
return @newid
end
go
create trigger t_tb on tb  instead of insert
as
begin
-- 只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id                            
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select   ' 2.1 '
select * from tb order by id
/*
id                            
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
go
drop table tb
drop function dbo.addid

---

create table tb(id varchar ( 30 ))
insert tb select ' 1 '
union all select ' 1.1 '
union all select ' 2 '
union all select ' 2.1 '
union all select ' 2.1.2 '
union all select ' 2.2 '
union all select ' 3 '
union all select ' 3.1 '
go
create function dbo.addid( @addid varchar ( 30 ), @id varchar ( 30 ))
returns varchar ( 30 )
as
begin
declare @newid varchar ( 30 ), @mid int
-- @addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex ( ' . ' , @addid ) > 0
      set @addid = left ( @addid , len ( @addid ) + 1 - charindex ( ' . ' , reverse ( @addid )))
else set @addid = ''
-- [1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid 
-- 前缀不同的直接返回
if @addid <> left ( @id , len ( @addid )) return @id
select @newid = @addid , @id = stuff ( @id , 1 , len ( @addid ), '' )
-- @id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex ( ' . ' , @id ) > 1
begin
  set @mid = left ( @id , charindex ( ' . ' , @id ) - 1 )
  set @id = stuff ( @id , 1 , charindex ( ' . ' , @id ) - 1 , '' )
end
else
begin
  set @mid = @id
  set @id = ''
end
-- [@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid + rtrim ( @mid + 1 ) + @id
return @newid
end
go
create trigger t_tb on tb  instead of insert
as
begin
-- 只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id                            
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select   ' 2.1 '
select * from tb order by id
/*
id                            
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
insert tb(id) select ' 2 '
select * from tb order by id
/*
id                            
------------------------------
1
1.1
2
3
3.1
3.2
3.2.2
3.3
4
4.1
*/
go
drop table tb
drop function dbo.addid

转载于:https://www.cnblogs.com/Peter-pan/archive/2008/04/25/1171388.html