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

无意间放入发现，觉得很有用，先记录下来

 

实现在删除数据后，自增列的值连续其处理思路如下：

在删除自增列所在表的记录时，将删除行的自增列的值保存在另外一个表，以便下次新增数据时，使用原来被删除的自增列的值。

实现步骤：

创建两个表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

 

在插入前需判断test_r表是否存在被删除的id ,若存在，则使用其test_r表中的记录作为插入行id栏位的值

如没有，则直接插入。通过test_id表的插入前触发器实现：

 

 

CREATE trigger iit_test_id on test_id
instead of insert
as
begin
declare @min_id int
declare @id int
declare @rowcount int
declare @rowcount_i int
declare @name varchar(20)
declare @sql varchar(8000)
create table #t(id int identity(1,1) ,name varchar(20) null,tag varchar(1) null)
insert into #t(name,tag) select name,'0' from inserted
--如果存在断号，取已经存在的断号。
if exists(select  * from test_r)
  begin
   -- 可以显示插入自增列。
   SET IDENTITY_INSERT test_id on
   --获取可用断号记录
   select @rowcount=count(*) from test_r
   --获取插入行的记录。
   select @rowcount_i=count(*) from inserted
   --当断号记录的数量大于插入数据的行数时，则所有的插入记录的 id均使用断号，故返回inserted 表中的所有行
   --当断号记录的数量小于插入数据的行数时，则所有的插入记录前的(断号记录总行数)行id均使用断号，故返回inserted 表中前(断号记录总行数)的行
   if @rowcount > @rowcount_i
    set @rowcount=@rowcount_i
   
   set @sql=''
   set @sql='declare cur_get cursor     for select top '+cast(@rowcount as varchar(20))+'  id,name from #t order by id '
    
   exec(@sql)
   open cur_get
   fetch cur_get into @id, @name
   while @@fetch_status=0
    begin
        
              select @min_id =min(r_id) from test_r
     if exists(  select min(r_id) from test_r)
      begin
              update #t set tag='1' where id=@id
      end
              insert into test_id(id,name)values(@min_id,@name)
     
              fetch cur_get into @id,@name
    end
   close cur_get
   deallocate cur_Get
   
   SET IDENTITY_INSERT test_id off

   --当断号记录的数量小于插入数据的行数时，使用断号记录的剩余行则不需要显示id插入
   if exists(select * from #t where tag='0')
    begin
     insert into test_id(name) select name from #t where  tag='0'
    end
   drop table #t
  end
 else
  -- 不存在断号就直接插入。
  insert into test_id(name )select name from inserted

 
end