begin
select @rc=count(id) from inserted
insert aa_tmp values(convert(varchar(20),@rc))
--如果缓冲区内行数多于一行,则退出触发器
if @rc>1 return
--编号前缀
select @bmbm=case left(cdepcode,2) when '01' then 'NN' when '02' then 'LZ' when '03' then 'GL' when '04' then 'WZ' when '05' then 'YL' when '06' then 'BS' when '07' then 'QZ' when '08' then 'HC' when '09' then 'BH' when '10' then 'GX' else 'XX' end from inserted
select @wzlb='GC' --目前先处理工程
select @rdid=id,@rq=right(convert(varchar(6),ddate,112),4) from inserted
select @codepre=@bmbm+'-'+@wzlb+@rq
--取流水号
--算法:以已有的流水号+1,再对照已有的流水号,如果not in的话,则说明该流水号可用
select @flownow=min(b.nextno) from
rdrecord a inner join
(select id as 'id',(convert(int,(right(ccode,4))) + 1) as 'nextno' from rdrecord where left(ccode,9)=@codepre and bisstqc=0) b on a.id=b.id
where b.nextno not in (select convert(int,(right(ccode,4))) from rdrecord where left(ccode,9)=@codepre and bisstqc=0)
--流水不存在则初始化为1
if @flownow is null set @flownow=1
insert aa_tmp values(convert(varchar(20),@flownow))
--将流水转换成字符
set @no = convert(varchar(6),@flownow)
set @no = case when @flownow<10 then '000'+@no when @flownow<100 then '00'+@no when @flownow<1000 then '0'+@no else @no end
insert aa_tmp values(convert(varchar(20),@no))
--更新单据编号
update rdrecord set ccode=@codepre+@no where id=@rdid
begin
select @rc=count(id) from inserted
insert aa_tmp values(convert(varchar(20),@rc))
--如果缓冲区内行数多于一行,则退出触发器
if @rc>1 return
--编号前缀
select @bmbm=case left(cdepcode,2) when '01' then 'NN' when '02' then 'LZ' when '03' then 'GL' when '04' then 'WZ' when '05' then 'YL' when '06' then 'BS' when '07' then 'QZ' when '08' then 'HC' when '09' then 'BH' when '10' then 'GX' else 'XX' end from inserted
select @wzlb='GC' --目前先处理工程
select @rdid=id,@rq=right(convert(varchar(6),ddate,112),4) from inserted
select @codepre=@bmbm+'-'+@wzlb+@rq
--取流水号
--算法:以已有的流水号+1,再对照已有的流水号,如果not in的话,则说明该流水号可用
select @flownow=min(b.nextno) from
rdrecord a inner join
(select id as 'id',(convert(int,(right(ccode,4))) + 1) as 'nextno' from rdrecord where left(ccode,9)=@codepre and bisstqc=0) b on a.id=b.id
where b.nextno not in (select convert(int,(right(ccode,4))) from rdrecord where left(ccode,9)=@codepre and bisstqc=0)
--流水不存在则初始化为1
if @flownow is null set @flownow=1
insert aa_tmp values(convert(varchar(20),@flownow))
--将流水转换成字符
set @no = convert(varchar(6),@flownow)
set @no = case when @flownow<10 then '000'+@no when @flownow<100 then '00'+@no when @flownow<1000 then '0'+@no else @no end
insert aa_tmp values(convert(varchar(20),@no))
--更新单据编号
update rdrecord set ccode=@codepre+@no where id=@rdid