identity 的问题，大家帮忙看看？

[zhangzhicong]

table:
CREATE TABLE [dbo].[person] (
[sid] [int] IDENTITY (1, 1) NOT NULL ,
[sname] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

record:
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'。

那里出错了。？？？

[liangpei2008]

Identity的标识列Sid不能被更新！

[zhangzhicong]

我想也是这里出错。
但是要怎么做才 对啊
具体的代码

[LouisXIV]

lz放弃这样的做法吧

虽然有Set Identity_Insert tablename on来设置成可以人工插入标示列

但是没有办法可以修改标示列

[hellowork]

楼主可以通过由触发器动态管理的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

[w2713]

建议你不用identity，利用存储过程插入、删除。或者使用触发器