实现在删除数据后,自增列的值连续其处理思路如下:
在删除自增列所在表的记录时,将删除行的自增列的值保存在另外一个表,以便下次新增数据时,使用原来被删除的自增列的值。
实现步骤:
创建两个表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