出200分，问一个SQL语句！！！！！

hackerning 2001-09-24 11:19:02

我建立了一个有一个字段自动从1增加的INT类型字段，也就是用了IDENTITY

但是问题是当我删除其中的一个字段后这些自动排序地增的字段不会变，

有什么SQL语句在删除一个字段后能让删除字段后的字段自动更新减一，保持一致的？？？？？？？

...全文

238 29 打赏收藏转发到动态举报

写回复

用AI写文章

29 条回复

切换为时间正序

请发表友善的回复…

发表回复

LiaoCheng 2001-10-22

打赏
举报

这种ID有什么意思，还是改变一种方式吧，或是从另外一个角度考虑，也许会有更好的效果

icevi 2001-10-22

打赏
举报

有必要吗?为了个没意义的ID号?

foolishchao 2001-10-22

打赏
举报

下面的方法在MsSql7中通过测试。
用另一个表保存最大ID，ID由 trigger 维护，后台操作。
通过锁定studentmaxid，每个时刻只有一个写操作在执行。

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_student_insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_student_insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_student_delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_student_delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[studentmaxid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[studentmaxid]
GO

CREATE TABLE [dbo].[student] (
[id] [int] NULL ,
[name] [char] (10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[studentmaxid] (
[id] [int] NOT NULL ,
[dates] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [name] ON [dbo].[student]([name]) ON [PRIMARY]
GO

CREATE INDEX [id] ON [dbo].[student]([id]) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER dbo.tr_student_insert ON [dbo].[student]
FOR INSERT
AS
set nocount on
declare @cnt int
select @cnt=count(*) from inserted
if @cnt=0
return

begin tran

declare @name char(10),@id int
--锁定 studentmaxid
update studentmaxid set dates=getdate()
--取出id
select @id=id from studentmaxid
if @id is null
begin
rollback tran
raiserror('System data is missing',16,1)
return
end

declare cr_student_insert cursor local for select name From Inserted
open cr_student_insert
fetch next from cr_student_insert into @name
set
while @@fetch_status=0
begin
set @id=@id+1
update student set id=@id where name=@name
fetch next from cr_student_insert into @name
end
close cr_student_insert
deallocate cr_student_insert
update studentmaxid set id=@id,dates=getdate()

commit tran

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER dbo.tr_student_delete ON [dbo].[student]
FOR DELETE
AS
set nocount on
declare @cnt int
select @cnt=count(*) from deleted
if @cnt=0
return
begin tran
declare @id int
--锁定 studentmaxid
update studentmaxid set dates=getdate()
select @id=id from studentmaxid
if @id is null
begin
rollback tran
raiserror('System data is missing',16,1)
return
end

declare cr_student_deleted cursor local for select id From deleted order by 1 desc
open cr_student_deleted
fetch next from cr_student_deleted into @id
while @@fetch_status=0
begin
update student set id=id-1 where id>@id
fetch next from cr_student_deleted into @id
end
close cr_student_deleted
deallocate cr_student_deleted
update studentmaxid set id=id-@cnt,dates=getdate()
commit tran

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

delete from studentmaxid
insert studentmaxid values(0,getdate())
delete from student
insert student(name) values('test1')
insert student(name) values('test2')
insert student(name) values('test3')
insert student(name) values('test4')
insert student(name) values('test5')
insert student(name) values('test6')

insert student(name) select rtrim(name)+'0' from student

delete student where id between 8 and 9

select * from student

hackerning 2001-10-22

打赏
举报

问题是并没有建立关联啊

Jack_Loo 2001-10-15

打赏
举报

如果有error的话，你肯定在表结构中建了关联，那么就相当麻烦了（你还要update其他表），不如不做。

zds0707 2001-10-15

打赏
举报

同意楼上的，其实这种做法就代来不必要的麻烦。
to weixxxp(想一想)　:
同意"IDENTITY字段一般用作关键字，并且只做识别用无实际意义，随便乱改什么"
但是"怎么保证完整性"好象不太对吧？

如果涉及数据复制，你就知道了，如：

有两台数据库服务器A、B，它们的出版物中有一表，关键字为IDENTITY,均有30条记录。

现在A删除第15条，B增加一条记录，想一想数据复制的结果会怎样？尤其是IDENTITY字段？

signboy 2001-10-14