if
object_id
(
'
T
'
)
is
not
null
drop
table
T
go
create
table
T(id
varchar
(
10
)
primary
key
,name
varchar
(
10
))
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
go
--
用触发器完成
create
trigger
tr_T_insert
on
T
instead
of
insert
as
begin
declare
@i
int
select
*
into
#
from
inserted
select
@i
=
isnull
(
max
(id),
1000000
)
from
T
update
#
set
id
=
right
(
1000000
+
@i
,
6
),
@i
=
@i
+
1
insert
T
select
*
from
#
end
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
go
--
测试
insert
T
select
1
,
'
a
'
union
all
--
这里1为任何值都不影响id插入的值
select
1
,
'
b
'
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
select
*
from
T
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/*
id name
---------- ----------
000001 a
000002 b
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
(所影响的行数为 2 行)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
*/
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
go
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
if
object_id
(
'
T
'
)
is
not
null
drop
table
T
go
create
table
T(id
char
(
6
),name
nvarchar
(
10
))
go
--
定义一个函数
if
object_id
(
'
test_fun
'
)
is
not
null
drop
function
test_fun
go
create
function
test_fun()
returns
char
(
6
)
as
begin
declare
@Max
char
(
6
)
select
@Max
=
right
(
'
000000
'
+
rtrim
((
isnull
(
max
(ID),
0
)
+
1
)),
6
)
from
T
return
@Max
end
go
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
alter
table
T
add
constraint
UQ_T_default
default
dbo.test_fun()
for
ID
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
go
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
insert
T(Name)
values
(
'
AA
'
)
insert
T(Name)
values
(
'
BB
'
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
--
或
insert
T
values
(dbo.test_fun(),
'
CC
'
)
go
select
*
from
T
go
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/*
id name
------ ----------
000001 AA
000002 BB
000003 CC
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
(所影响的行数为 3 行)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
*/
drop
table
T
drop
function
test_fun
go
<!-- [if gte mso 9]><![endif]--><!-- [if gte mso 9]><![endif]--><!-- [if gte mso 10]>
--
用數據庫觸發器
(
處理自增列斷號
)
if object_id('T') is not null
drop table T
go
create table T(id INT IDENTITY,name nvarchar(10))
go
CREATE TRIGGER Tr_dT ON T
INSTEAD OF DELETE
AS
SET NOCOUNT ON ;
BEGIN
DECLARE @MinID INT
SELECT @MinID=MIN(ID)-1 FROM DELETED
DELETE T WHERE ID IN(SELECT ID FROM deleted)
SELECT ID=ID*1,NAME INTO # FROM T WHERE ID>@MinID
DELETE T WHERE ID>@MinID
UPDATE # SET ID=@MinID,@MinID=@MinID+1
SET IDENTITY_INSERT ON ;
INSERT INTO T(ID,Name)SELECT ID,Name FROM #
SET IDENTITY_INSERT OFF;
DBCC CHECKIDENT('T',RESEED,@MinID)
DROP TABLE #
END
go
--新增數據
insert T(Name) values('AA')
insert T(Name) values('BB')
insert T(Name) values('CC')
insert T(Name) values('DD')
insert T(Name) values('EE')
GO
SELECT * FROM T
/*
id name
1 AA
2 BB
3 CC
4 DD
5 EE
*/
go
--測刪除
DELETE T WHERE ID IN(2,3)
go
insert T(Name) values('FF')
SELECT * FROM T
/*
id name
1 AA
2 DD
3 EE
4 FF
*/
go
drop table T