create table tt(id int identity(1,1),name varchar(10)) insert into tt select 'aa' insert into tt select 'bb' insert into tt select 'cc' insert into tt select 'dd' insert into tt select 'ee' go--创建补号添加存储过程 create proc insert_wsp @name varchar(10) as set identity_insert tt on insert into tt(id,name) select isnull(min(a.number),(select max(id)+1 from tt)),@name from master..spt_values a inner join (select mid=max(id) from tt)b on a.number between 1 and mid left join tt on a.number=tt.id where type='p' and tt.id is null set identity_insert tt off go --删除4,5 delete tt where id>3 go--添加 exec insert_wsp 'ff' exec insert_wsp 'gg' exec insert_wsp 'hh'--查询结果 select * from tt --结果: id name ----------- ---------- 1 aa 2 bb 3 cc 4 ff 5 gg 6 hh
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(4)) insert [tb] select 1,'张三' union all select 2,'张四' union all select 3,'张五' union all select 4,'张六' union all select 5,'张七' union all select 6,'张八' union all select 7,'张九' union all select 8,'张十' go --插入触发器 --支持批量插入 if object_id('tt') is not null drop trigger tt go create trigger tt on tb instead of insert as begin insert into tb select id=(select count(1) from inserted where name>i.name)+(select max(id) as id from tb)+1,name from inserted i endinsert into tb(name) select'王五' union all select 'bbb' select * from [tb] /* id name ----------- ---- 1 张三 2 张四 3 张五 4 张六 5 张七 6 张八 7 张九 8 张十 9 王五 10 bbb(所影响的行数为 10 行)*/ --删除触发器 --不支持批量删除 if object_id('dd') is not null drop trigger dd go create trigger dd on tb for delete as begin declare @id int select @id=id from deleted update tb set id=id-1 where id>@id enddelete from tb where id=4select * from [tb] /* ----------- ---- 1 张三 2 张四 3 张五 4 张七 5 张八 6 张九 7 张十 8 王五 9 bbb(所影响的行数为 9 行) */
-- 测试环境if object_id('tb') is not null drop table tb go create table tb ( id int, name varchar(20) ) go insert tb select 1 ,'张三'union all select 2 ,'李四'union all select 3 ,'王五'union all select 4 ,'王启'union all select 5 ,'李乐' go --触发器 create trigger trd_tb on tb for delete as begin set nocount on declare @id int,@name varchar(20) declare cur cursor for select id,name from deleted order by id open cur fetch cur into @id,@name while @@fetch_status=0 begin update tb set id=id-1 where id>@id fetch cur into @id,@name end close cur deallocate cur end go -- 查询 delete tb where name='王五' select * from tb -- 结果 /* id name ----------- -------------------- 1 张三 2 李四 3 王启 4 李乐(4 行受影响) */
SQL 2005的实现方式 update T set XH=T.NXH FROM (SELECT ROW_NUMBER() OVER (ORDER BY XH) AS NXH, ID FROM T ) T1 WHERE T.ID=T1.ID
其次,如果直要这么做,变通的处理办法参考:http://blog.csdn.net/htl258/archive/2009/07/22/4369943.aspx
insert into tt select 'aa'
insert into tt select 'bb'
insert into tt select 'cc'
insert into tt select 'dd'
insert into tt select 'ee'
go--创建补号添加存储过程
create proc insert_wsp
@name varchar(10)
as
set identity_insert tt on
insert into tt(id,name) select isnull(min(a.number),(select max(id)+1 from tt)),@name from master..spt_values a inner join (select mid=max(id) from tt)b on a.number between 1 and mid
left join tt on a.number=tt.id
where type='p' and tt.id is null
set identity_insert tt off
go
--删除4,5
delete tt where id>3
go--添加
exec insert_wsp 'ff'
exec insert_wsp 'gg'
exec insert_wsp 'hh'--查询结果
select * from tt
--结果:
id name
----------- ----------
1 aa
2 bb
3 cc
4 ff
5 gg
6 hh
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'张四' union all
select 3,'张五' union all
select 4,'张六' union all
select 5,'张七' union all
select 6,'张八' union all
select 7,'张九' union all
select 8,'张十'
go
--插入触发器
--支持批量插入
if object_id('tt') is not null drop trigger tt
go
create trigger tt on tb
instead of insert
as
begin
insert into tb
select id=(select count(1) from inserted where name>i.name)+(select max(id) as id from tb)+1,name from inserted i
endinsert into tb(name) select'王五' union all select 'bbb'
select * from [tb]
/*
id name
----------- ----
1 张三
2 张四
3 张五
4 张六
5 张七
6 张八
7 张九
8 张十
9 王五
10 bbb(所影响的行数为 10 行)*/
--删除触发器
--不支持批量删除
if object_id('dd') is not null drop trigger dd
go
create trigger dd on tb
for delete
as begin
declare @id int
select @id=id from deleted
update tb set id=id-1 where id>@id
enddelete from tb where id=4select * from [tb]
/*
----------- ----
1 张三
2 张四
3 张五
4 张七
5 张八
6 张九
7 张十
8 王五
9 bbb(所影响的行数为 9 行)
*/
go
create table tb
(
id int,
name varchar(20)
)
go
insert tb
select 1 ,'张三'union all
select 2 ,'李四'union all
select 3 ,'王五'union all
select 4 ,'王启'union all
select 5 ,'李乐'
go
--触发器
create trigger trd_tb on tb for delete
as
begin
set nocount on
declare @id int,@name varchar(20)
declare cur cursor for select id,name from deleted order by id
open cur
fetch cur into @id,@name
while @@fetch_status=0
begin
update tb set id=id-1 where id>@id
fetch cur into @id,@name
end
close cur
deallocate cur
end
go
-- 查询
delete tb where name='王五'
select * from tb
-- 结果
/*
id name
----------- --------------------
1 张三
2 李四
3 王启
4 李乐(4 行受影响)
*/
update T set XH=T.NXH
FROM (SELECT ROW_NUMBER() OVER (ORDER BY XH) AS NXH, ID FROM T ) T1
WHERE T.ID=T1.ID