网站运营seo文章大全提供全面的站长运营经验及seo技术!使用sql server创建数字类型的字段，可以设置为自动编号。但很多时候并不能满足我们的需求，例如为学生编号时，可能要用到年级、系别等再加上流水号进行编号。下面给一个简单的例子，使用触发器来进行自动编号。
--创建测试表
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[usertable]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[usertable]
go
create table usertable(userid varchar(20),username nvarchar(20))
go
--创建触发器
create trigger tg_insert on usertable
for insert
as
declare @username nvarchar(20)
declare @userid varchar(20)
declare @num int
declare @strnum varchar(20)
declare @prefix varchar(10)
declare @numlen int
declare @strdate varchar(20)
--获取当前日期
set @strdate=substring(convert(varchar(10),getdate(),112),1,8)
--设置流水号的长度
set @numlen = 4
--设置前缀
set @prefix = 's'
select @userid=max(userid) from usertable
where userid like @prefix + @strdate + '%'
if @userid is null
set @num=0
else
set @num=cast(replace(@userid,@prefix + @strdate ,'') as int)
set @num = @num + 1
set @strnum = cast(@num as varchar(10))
while(len(@strnum)<@numlen)
set @strnum = '0' + @strnum
set @[email protected] + @strdate + @strnum
select @username=username from inserted
rollback
insert into usertable values(@userid,@username)
go
--测试
insert into usertable(username) values('aa')
go
insert into usertable(username) values('bb')
go
insert into usertable(username) values('cc')
go
--显示数据
select * from usertable
go
