创建自动生成单号或者流水号的触发器

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