如何实现自编号

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 + @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

/**/ /*
id         name       
---------- ---------- 
000001     a
000002     b

（所影响的行数为 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
/**/ /*
id     name       
------ ---------- 
000001 AA
000002 BB
000003 CC

（所影响的行数为 3 行）


*/
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