实现在删除数据后，自增列的值连续代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】实现在删除数据后，自增列的值连续

作者:mcgtts / 发布于2013/12/31/ 956

实现步骤：

创建两个表test_id(自增列所在表),test_r(记录被删除的自增列其值)

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

CREATE TABLE [dbo].[test_id] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

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

CREATE TABLE [dbo].[test_r] (
[r_id] [int] NULL
) ON [PRIMARY]
GO



在删除test_id 的记录时，将起响应id保存到test_r表，通过test_id 表的delete触发器实现

CREATE TRIGGER dt_test_id ON [dbo].[test_id]
FOR DELETE
AS
begin
declare @row int
set @row=0
if exists(select * from deleted )
 begin
 insert into test_r(r_id) select id from deleted
 end

end

向表test_id插入数据时，判断其id是否存在与test_r表中，如存在则删除id在test_r值，通过test_id的插入触发器实现：

CREATE TRIGGER it_test_id ON [dbo].[test_id]
FOR INSERT
AS
begin
if @@rowcount=0 return
declare @row int
set @row=0
if exists(select * from inserted )
 begin
 delete from test_r where exists(select id from inserted where test_r.r_id=inserted.id )
 end

end

试试其它关键字

　自增列值连续　

同语言下

可能有用的

mcgtts贡献的其它代码(6)