27,579
社区成员
发帖
与我相关
我的任务
分享
create table tab1(xuhao char(10))
go
create trigger tri_test
on tab1
instead of insert
as
declare @s varchar(10)
declare @left varchar(10),@right varchar(10)
select * into # from inserted
update #
set xuhao=right(datename(year,getdate()),2)+left(xuhao,charindex('-',xuhao)-1)+'-'
+right('0000'+rtrim(substring(xuhao,charindex('-',xuhao)+1,10)),4)
insert into tab1
select * from #
go
insert into tab1(xuhao) values('1-88')
insert into tab1(xuhao) values('2-008')
insert into tab1(xuhao) values('1-8')
select * from tab1
go
create table tab1(xuhao char(10))
go
create trigger tri_test
on tab1
instead of insert
as
declare @s varchar(10)
declare @left varchar(10),@right varchar(10)
if @@rowcount>1 return
select @s=xuhao from inserted i
set @left=left(@s,charindex('-',@s)-1)
set @right=rtrim(substring(@s,charindex('-',@s)+1,10))
set @left=right(datename(year,getdate()),2)+@left
set @right=right('0000'+@right,4)
set @s=@left+'-'+@right
insert into tab1(xuhao) values(@s)
go
insert into tab1(xuhao) values('1-88')
insert into tab1(xuhao) values('2-008')
insert into tab1(xuhao) values('1-8')
select * from tab1
go
drop table tab1
/*
xuhao
----------
081-0088
082-0008
081-0008
*/
create trigger tri_test
on tab1
instead of insert
as
declare @s varchar(10)
declare @left varchar(10),@right varchar(10)
if @@rowcount>0 return
select @s=xuhao from inserted i
set @left=left(@s,charindex('-',@s)-1)
set @right=substring(@s,charindex('-',@s)+1,10)
set @left=datename(year,getdate())+@left
set right=right('0000'+@right,4)
set @s=@left+'-'+@right
insert into tab1(xuhao) values(@s)
go