create trigger t_JianChaHuaYan
on JianChaHuaYan
instead of insert
as
begin
declare @BianHao varchar(32),@ls int,@head varchar(20),@KeShiBianMa varchar(32),@CaoZuoYuanBianMa varchar(32)
select * into #tb from inserted
select @KeShiBianMa = KeShi.BianMa From KeShi,JiuYi,#tb Where JiuYi.科室ID= KeShi.科室ID and JiuYi.就医ID = #tb.就医ID
select @CaoZuoYuanBianMa = Operator.编号 from Operator ,JiuYi,#tb where Operator.操作员ID= JiuYi.操作员ID and JiuYi.就医ID = #tb.就医ID
set @head='J'+convert(varchar,getdate(),112)+@KeShiBianMa+@CaoZuoYuanBianMa
select @BianHao=max(BianHao) from JianChaHuaYan where BianHao like @head+'%'
if @BianHao is null
set @ls=0
else
begin
set @ls=cast(right(@BianHao,4) as int)
end
set @Bianhao=right( cast((@ls+10001) as varchar),4)
update #tb set BianHao=@head+@BianHao
insert into JianChaHuaYan select 就医ID, BianHao, ShiFuJiaoFei, ChanShengShiJian, JiaoFeiShiJian from #tb
end
go