CREATE TRIGGER [dbo].[TU_OverUnder] ON  [dbo].[TmpDrugHavaleh]
   AFTER UPDATE
AS 
BEGIN
  Declare @IdHavaleh  Bigint
  Declare @KindFlag   Char(1)
  Declare @KCode Varchar(20)
  Declare @TakhfifFlag Varchar(2)
  Declare @TakhfifFlagIns Varchar(2)
  Declare @TakhfifFlagDel Varchar(2)
  Declare @EditFlag Char(1)
  Declare @Price Money
  Declare @Type Char(1)
  Declare @SumForosh Money
  Select  @IdHavaleh = TmpDrugH.Id_Havaleh, @KindFlag= Kind_Flag,
  @TakhfifFlag = Takhfif_Flag, @KCode = K_Code  From Inserted 
  Inner Join TmpDrugH On TmpDrugH.Id_Havaleh = Inserted.Id_Havaleh
  Where Inserted.K_Code in ('1','2','3')  
  Group By TmpDrugH.Id_Havaleh, Kind_Flag, Takhfif_Flag, K_Code
  Select @SumForosh = Sum(K_Qty1 * Price_Forosh)  From TmpDrugHavaleh 
  Where Id_Havaleh = @IdHavaleh 
  Select @TakhfifFlagIns = IsNull(Takhfif_Flag,'-') From Inserted Group By Takhfif_Flag
  Select @TakhfifFlagDel = IsNull(Takhfif_Flag,'-') From Deleted Group By Takhfif_Flag
  IF (@TakhfifFlagIns <> @TakhfifFlagDel )
    IF @KindFlag = '1' 
    Begin
      IF (@TakhfifFlag = '0' Or @TakhfifFlag = '3')
      Begin
        IF @KCode = '1'
        Begin 
          Set @Price = 11000
          Set @type = '0'        
        End
        Else If @KCode = '2'
        Begin
          Set @Price = 9000
          Set @type = '0'        
        End
        Else If @KCode = '3'
        Begin
          Set @Price = 8000
          Set @type = '0'        
        End
      End
      /*Else
      Begin
        IF @KCode = '1'
        Begin 
          Set @Price = 1700
          Set @type = '0'        
        End
        Else If @KCode = '2'
        Begin
          Set @Price = 1500
          Set @type = '1'        
        End
        Else If @KCode = '3'
        Begin
          Set @Price = 2100
          Set @type = '1'        
        End
      End*/
      
      Update Tmp_Over_Under_Factor_Drug Set Price = @Price, [Percent] = (@Price * 100 /@SumForosh), Type = @Type
      Where Id_Havaleh = @IdHavaleh And Code_Over_Under = 28
    End
END
