写了一个流水号的触发器.
create table test(
[id] char(13),
[data] varchar(200)
)
go
if exists(select name from sysobjects
where name='auto_id' and type='tr')
drop trigger auto_id
go
create trigger auto_id on test
instead of insert
as
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @new_id char(13)
declare @new_no char(4)
declare @data varchar(200)
select @year=convert(varchar(4),datepart(year,getdate()))
select @month=convert(varchar(2),datepart(month,getdate()))
if len(@month)=1
select @month='0'+@month
select @day=convert(varchar(2),datepart(day,getdate()))
if len(@day)=1
select @day='0'+@day
select @data=data from inserted
select @new_no=right('0000'+convert(varchar(4),max(convert(int,right(id,4)))+1),4) from test
if @new_no='' or @new_no is null
select @new_no='0001'
select @new_id=@year+@month+@day+'-'+@new_no
begin
insert into test([id],[data]) values(@new_id,@data)
if @@rowcount=0
begin
raiserror('error',16,1)
rollback tran
end
end
go
insert into test(data) values('purana')
select * from test