MSSQL自動產生編號格式
怎樣由 SQL Server 2000 系統自動編號產生格式如:BV+YYYYMM+編號BV是固定字串
YYYYMM是年月
編號是0到9的數字
使用觸發器來處理[code]create table test(
id varchar(20),
sname varchar(20)
)[/code][code]create TRIGGER [test_insert] ON [dbo].[test]
INSTEAD OF Insert
AS
declare @str varchar(20)
declare @i integer
set @str = 'BV'+left(convert(char,getdate(),112),6)
select @i=isnull(max(cast(right(rtrim(id),len(id)-8) as integer)),0) from
(select id from test where id like @str+'%') a
set @i=@i+1
Insert INTO TEST
Select @STR++cast(@i as char)as id,sname from inserted
[/code]上面建好後執行：
insert into test(sname) values('test')
id欄位就會自動編好號
頁:
[1]