-- 强烈不建议用于生产环境 if OBJECT_ID('tab') is not null drop table tab; go create table tab (id char(4) PRIMARY KEY); go create trigger trg_tab_id on tab instead of insert as set nocount on; declare c cursor local for select id from inserted order by id; open c;
declare @id char(4), @mid char(4); while 1=1 begin fetch next from c into @id; if @@fetch_status <> 0 break;
if exists (select * from tab where id=@id) begin select @mid=id from tab t where id>=@id and not exists (select * from tab where right(id,2)=right(t.id,2)+1); update tab set id=left(id,2)+left('0'+ltrim(right(id,2)+1),2) where id between @id and @mid; end insert into tab values(@id); end
close c; deallocate c; goinsert into tab select 'VP01' union all select 'VP02' union all select 'VP03' union all select 'VP04';insert into tab select 'VP02' union all select 'VP05';select * from tab; /* VP01 VP02 VP03 VP04 VP05 VP06 */
VP01
VP02 --插入值
VP03 --原VP02
VP04 --原VP03
VP05 --原VP04
……
依次将下面的VP序列都重排吗?
-- 强烈不建议用于生产环境
if OBJECT_ID('tab') is not null
drop table tab;
go
create table tab (id char(4) PRIMARY KEY);
go
create trigger trg_tab_id on tab
instead of insert
as
set nocount on;
declare c cursor local for select id from inserted order by id;
open c;
declare @id char(4), @mid char(4);
while 1=1
begin
fetch next from c into @id;
if @@fetch_status <> 0 break;
if exists (select * from tab where id=@id)
begin
select @mid=id from tab t
where id>=@id and not exists (select * from tab where right(id,2)=right(t.id,2)+1);
update tab set id=left(id,2)+left('0'+ltrim(right(id,2)+1),2) where id between @id and @mid;
end
insert into tab values(@id);
end
close c;
deallocate c;
goinsert into tab
select 'VP01' union all select 'VP02' union all
select 'VP03' union all select 'VP04';insert into tab
select 'VP02' union all select 'VP05';select * from tab;
/*
VP01
VP02
VP03
VP04
VP05
VP06
*/