create trigger A_UP on TF_POS for insert 
as 

begin
  declare @area varchar(30)
  declare @sal_no varchar(30)
  declare @yy varchar(4)
  declare @d Datetime
  declare @os_id varchar(10)
  declare @OS_NO varchar(30)
  declare @iCount int
  declare @iCount2 int
  declare @up numeric(28,8)
  declare @PRD_NO varchar(30)
  declare @ITM int

  set nocount on 
  select @OS_ID=OS_ID,@PRD_NO=PRD_NO,@itm=itm,@OS_NO=OS_NO from inserted 
  if @OS_ID='SO' 
    begin
      select @SAL_NO=B.SAL_NO,@D=B.OS_DD,@AREA=C.CUS_ARE from inserted A,MF_POS B,CUST C 
          where A.OS_NO=B.OS_NO and B.CUS_NO=C.CUS_NO
      
      select @iCount=count(*) from up_A2 where SAL_NO=@SAL_NO and PRD_NO=@PRD_NO and cus_are=@AREA and 
             S_DD<=@D and E_DD>=@D
      if @iCount>=1   --取得按業(yè)務(wù)員取得的單價
         begin
           select top 1 @up=qty  from up_A2 where SAL_NO=@SAL_NO and PRD_NO=@PRD_NO and cus_are=@AREA and 
               S_DD<=@D and E_DD>=@D order by S_DD desc
         end
       else
          begin
            select @up=QTY from up_A1 where prd_no=@PRD_NO and yy=datepart(year,@D) 
          end
       select @iCount=count(*) from tf_pos_z where os_no=@OS_NO and ITM=@ITM
       if @iCount=0 
          begin
             insert into tf_pos_Z(OS_ID,OS_NO,ITM,A) values ('SO',@OS_NO,@ITM,@UP)
          end
      else
        begin
             update tf_pos_Z set A=@up where OS_NO=@OS_NO and ITM=@ITM
        end

    end
  set nocount off

end