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

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

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

实现步骤：

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

1.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_id]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)   
2.drop table [dbo].[test_id]   
3.GO   
4.  
5.CREATE TABLE [dbo].[test_id] (   
6. [id] [int] IDENTITY (1, 1) NOT NULL ,   
7. [name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL  
8.) ON [PRIMARY]   
9.GO   
10.  
11.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_r]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)   
12.drop table [dbo].[test_r]   
13.GO   
14.  
15.CREATE TABLE [dbo].[test_r] (   
16. [r_id] [int] NULL  
17.) ON [PRIMARY]   
18.GO  

 

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

1.CREATE TRIGGER dt_test_id ON [dbo].[test_id]   
2.FOR  DELETE  
3.AS  
4.begin  
5. declare @row int  
6. set @row=0   
7. if exists(select * from deleted )   
8.  begin  
9.   insert into test_r(r_id) select id from deleted   
10.  end  
11.    
12.end  

  

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

1.CREATE TRIGGER it_test_id ON [dbo].[test_id]   
2.FOR INSERT  
3.AS  
4.begin  
5. if @@rowcount=0 return  
6. declare @row int  
7. set @row=0   
8. if exists(select * from inserted )   
9.  begin  
10.   delete from test_r where  exists(select id from inserted where  test_r.r_id=inserted.id )   
11.  end  
12.    
13. end  

 

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

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

1.CREATE trigger iit_test_id on test_id   
2.instead of insert  
3.as  
4.begin  
5.declare @min_id int  
6.declare @id int  
7.declare @rowcount int  
8.declare @rowcount_i int  
9.declare @name varchar(20)   
10.declare @sql varchar(8000)   
11.create table #t(id int identity(1,1) ,name varchar(20) null,tag varchar(1) null)   
12.insert into #t(name,tag) select name,'0' from inserted   
13.--如果存在断号，取已经存在的断号。   
14.if exists(select  * from test_r)   
15.  begin  
16.   -- 可以显示插入自增列。   
17.   SET IDENTITY_INSERT test_id on  
18.   --获取可用断号记录   
19.   select @rowcount=count(*) from test_r   
20.   --获取插入行的记录。   
21.   select @rowcount_i=count(*) from inserted   
22.   --当断号记录的数量大于插入数据的行数时，则所有的插入记录的 id均使用断号，故返回inserted 表中的所有行   
23.   --当断号记录的数量小于插入数据的行数时，则所有的插入记录前的(断号记录总行数)行id均使用断号，故返回inserted 表中前(断号记录总行数)的行   
24.   if @rowcount > @rowcount_i   
25.    set @rowcount=@rowcount_i   
26.      
27.   set @sql=''  
28.   set @sql='declare cur_get cursor     for select top '+cast(@rowcount as varchar(20))+'  id,name from #t order by id '  
29.       
30.   exec(@sql)   
31.   open cur_get   
32.   fetch cur_get into @id, @name  
33.   while @@fetch_status=0   
34.    begin  
35.           
36.              select @min_id =min(r_id) from test_r   
37.     if exists(  select min(r_id) from test_r)   
38.      begin  
39.              update #t set tag='1' where id=@id   
40.      end  
41.              insert into test_id(id,name)values(@min_id,@name)   
42.        
43.              fetch cur_get into @id,@name  
44.    end  
45.   close cur_get   
46.   deallocate cur_Get   
47.      
48.   SET IDENTITY_INSERT test_id off  
49.  
50.   --当断号记录的数量小于插入数据的行数时，使用断号记录的剩余行则不需要显示id插入   
51.   if exists(select * from #t where tag='0')   
52.    begin  
53.     insert into test_id(name) select name from #t where  tag='0'  
54.    end  
55.   drop table #t   
56.  end  
57. else  
58.  -- 不存在断号就直接插入。   
59.  insert into test_id(name )select name from inserted   
60.  
61.    
62.end