如何实现自编号

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