table:
CREATE TABLE [dbo].[person] (
[sid] [int] IDENTITY (1, 1) NOT NULL ,
[sname] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOrecord:
sid sname
----------- ----------
1 zhang
2 wei
3 zhi
4 cong
5 xiao
6 xia
7 li
8 wen
9 nuan
10 zhen
11 shao
12 qing
13 sai
14 ping 想法:
我假如删除了,中间的一条记录,然后后面的sid自动减一。
eg:
我删除了
6 xia 这条记录,原来7 li 的自动变成 6 li 后面类似。我的做法用了触发器:
CREATE TRIGGER del_t ON [dbo].[person]
FOR DELETE
AS
update person
set sid=a.sid-1
from person a,deleted d
where a.sid>d.sid 无法更新标识列 'sid'。那里出错了。???
CREATE TABLE [dbo].[person] (
[sid] [int] IDENTITY (1, 1) NOT NULL ,
[sname] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOrecord:
sid sname
----------- ----------
1 zhang
2 wei
3 zhi
4 cong
5 xiao
6 xia
7 li
8 wen
9 nuan
10 zhen
11 shao
12 qing
13 sai
14 ping 想法:
我假如删除了,中间的一条记录,然后后面的sid自动减一。
eg:
我删除了
6 xia 这条记录,原来7 li 的自动变成 6 li 后面类似。我的做法用了触发器:
CREATE TRIGGER del_t ON [dbo].[person]
FOR DELETE
AS
update person
set sid=a.sid-1
from person a,deleted d
where a.sid>d.sid 无法更新标识列 'sid'。那里出错了。???
----建立测试数据表
CREATE TABLE person(
[id] int IDENTITY (1,1) NOT NULL,
sid int,
sname char(10) COLLATE Chinese_PRC_CI_AS NULL
)
----添加测试数据
insert person(sid,sname) values(1,'zhang')
insert person(sid,sname) values(2,'wei')
insert person(sid,sname) values(3,'zhi')
insert person(sid,sname) values(4,'cong')
insert person(sid,sname) values(5,'xiao')
insert person(sid,sname) values(6,'xia')
insert person(sid,sname) values(7,'li')
insert person(sid,sname) values(8,'wen')
insert person(sid,sname) values(9,'nuan')
insert person(sid,sname) values(10,'zhen')
insert person(sid,sname) values(11,'shao')
insert person(sid,sname) values(12,'qing')
insert person(sid,sname) values(13,'sai')
insert person(sid,sname) values(14,'ping')
----查看测试数据
select * from person
go
----创建DELETE触发器,在删除行时自动重新调整sid列的值
create trigger triperson on person
for delete
as
begin transaction
declare @Maxsid int
declare @Count int
select top 1 @Maxsid = sid from deleted order by sid desc /*获得被删除记录的最大sid值*/
select @Count = count(1) from deleted /*获得被删除记录的数量*/
update person set sid = sid-@count where sid > @Maxsid /*更新后面行的sid*/
if @@error > 0
begin
raiserror('Sorry,Rebuild column ''sid'' failed! ',16,1)
rollback
return
end
commit transaction
go
----创建INSERT触发器,添加记录时sid自动从上一行的值加1(省略错误捕捉步骤)
create trigger triperson_i on person
for insert
as
update person set sid = (select top 1 (sid +1) as sid from person order by sid desc)
from person,inserted where person.id = inserted.id /*将新添加记录的sid设为上一行的值+1*/
go
----测试:批量删除3,4,5号id
delete from person where id in(3,4,5)
----查看重建id效果
select * from person
----测试:只删除一条记录
delete from person where id = 6
select * from person
----测试:插入新记录
insert person default values
insert person default values
insert person default values
select * from person order by sid
----清除测试环境
drop table person