if
object_id
(
'
T
'
)
is
not
null
drop
table
T
go
create
table
T(id
varchar
(
10
)
primary
key
,name
varchar
(
10
))
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
+
rtrim
(
@i
),
6
),
@i
=
@i
+
1
insert
T
select
*
from
#
end
go
--
测试
insert
T
select
1
,
'
a
'
union
all
--
这里1为任何值都不影响id插入的值
select
1
,
'
b
'
select
*
from
T
/*
idname
--------------------
000001a
000002b
（所影响的行数为2行）
*/
go
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
alter
table
T
add
constraint
UQ_T_default
default
dbo.test_fun()
for
ID
go
insert
T(Name)
values
(
'
AA
'
)
insert
T(Name)
values
(
'
BB
'
)
--
或
insert
T
values
(dbo.test_fun(),
'
CC
'
)
go
select
*
from
T
go
/*
idname
----------------
000001AA
000002BB
000003CC
（所影响的行数为3行）
*/
drop
table
T
drop
function
test_fun
go
<!-- [endif]-->
<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--> <!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->
-- 用數據庫觸發器 ( 處理自增列斷號 )
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 T ON ;
INSERT INTO T ( ID , Name ) SELECT ID , Name FROM #
SET IDENTITY_INSERT T 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
