利用触发器实现标识列连续。(支持批量插入)

1. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
2. drop table [dbo].[ttt]
3. GO
5. /****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:26 ******/
6. CREATE TABLE [dbo].[ttt] (
7.     [id] [int] IDENTITY (1, 1) NOT NULL ,
8.     [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
9.     [time] [datetime] NULL 
10. ) ON [PRIMARY]
11. GO
13. ALTER TABLE [dbo].[ttt] ADD 
14.     CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 
15.     (
16.         [id]
17.     )  ON [PRIMARY] 
18. GO
20. insert into ttt(name,time) values('logan',getdate());
21. insert into ttt(name,time) values('peter',getdate());
22. insert into ttt(name,time) values('man',getdate());
23. insert into ttt(name,time) values('lida',getdate());
24. insert into ttt(name,time) values('fcuandy',getdate());
26. select * from ttt
27. /*
28. 1   logan   2008-12-15 17:36:37.780
29. 2   peter   2008-12-15 17:36:37.780
30. 3   man 2008-12-15 17:36:37.780
31. 4   lida    2008-12-15 17:36:37.780
32. 5   fcuandy 2008-12-15 17:36:37.793
33. */
36. GO
37. CREATE TRIGGER tr ON ttt
38. INSTEAD OF INSERT
39. AS
40.     SET IDENTITY_INSERT ttt ON
41.     DECLARE @n INT
42.     SELECT @n=MAX(id) FROM ttt
43.     ;WITH fc AS
44.     (
45.         SELECT n=1
46.         UNION ALL
47.         SELECT nn=n+1 FROM fc WHERE n<@n
48.     ),fc1 AS
49.     (
50.     SELECT n FROM fc a
51.     LEFT JOIN ttt b
52.         ON a.n = b.id
53.         WHERE b.id IS NULL
54.     )
55.     INSERT ttt(id,name,time) SELECT n,name,time
56.         FROM
57.         (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a
58.     INNER JOIN
59.         (
60.         SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
61.         ) b
62.         ON a.idx=b.idx
63.     DECLARE @r INT
64.     SELECT @r=@@ROWCOUNT
65.     
66.     SET IDENTITY_INSERT ttt OFF
67.         INSERT ttt(name,time) SELECT name,time FROM 
68.             (
69.                 SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
70.             ) x
71.             WHERE idx>@r
72.     
74. GO
75. DELETE FROM ttt WHERE name = 'peter' OR name='lida'
76. GO
77. INSERT ttt SELECT 'xxx',getdate()
78. INSERT ttt SELECT 'yyy',GETDATE()
79. GO
80. SELECT * FROM ttt
81. /*
82. 1   logan   2008-12-15 17:37:20.967
83. 2   xxx 2008-12-15 17:37:21.013
84. 3   man 2008-12-15 17:37:20.967
85. 4   yyy 2008-12-15 17:37:21.030
86. 5   fcuandy 2008-12-15 17:37:20.967
87. */
89. DELETE FROM ttt WHERE name ='xxx' OR name='yyy'
91. INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()
93. SELECT * FROM ttt
95. /*
96. 1   logan   2008-12-15 17:38:29.450
97. 2   roy_88  2008-12-15 17:38:29.530
98. 3   man 2008-12-15 17:38:29.467
99. 4   limpire 2008-12-15 17:38:29.530
100. 5   fcuandy 2008-12-15 17:38:29.467
101. 6   熊   2008-12-15 17:38:29.530
102. */