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