如何实现自编号

 http://blog.csdn.net/roy_88/article/details/1424370

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