CREATE TRIGGER [dbo].[TI_SanadStandard] ON [dbo].[SanadStandard]
  FOR insert
  AS 

   declare @Code Varchar(20)
   declare @P_Code Varchar(20)
   declare @Id_Sanad Smallint
   declare @Acc_Level smallint
   declare @Acc_Name NVarchar(60)
  
   declare @New_Bestan Money
   declare @NEW_Bedeh Money

   declare @FlagInsert smallint	  

   DECLARE CursorFetch CURSOR FOR
   SELECT Id_Sanad FROM Inserted
   GROUP BY Inserted.Id_Sanad
   OPEN CursorFetch
   FETCH NEXT FROM CursorFetch INTO @Id_Sanad
   WHILE @@FETCH_STATUS = 0 
   BEGIN
   SELECT @Id_Sanad = Id_Sanad From inserted i Where Acc_Code=i.Acc_Code AND Id_Sanad = @Id_Sanad 
   Select @Code = acc_code From inserted i Where Acc_Code=i.Acc_Code AND Id_Sanad = @Id_Sanad 
   Select @P_Code = Dbo.GetParent(@Code)

  SELECT @FlagInsert  =  i.FlagInsert  From inserted i Where Acc_Code=i.Acc_Code  AND Id_Sanad = @Id_Sanad  
  Select @NEW_Bedeh  =Sum( Bedeh),@Acc_Level=i.Acc_Level From inserted i Where Acc_Code=i.Acc_Code AND Id_Sanad = @Id_Sanad  group by Acc_level 
  Select @NEW_Bestan  =Sum(Bestan),@Acc_Level=i.Acc_Level From inserted i Where Acc_Code=i.Acc_Code AND Id_Sanad = @Id_Sanad  GROUP by Acc_level 
  Select @Acc_Name = Acc_Name From Account Where Acc_Code=@P_Code
  Select @Acc_Level = Acc_Level From Account Where Acc_Code=@P_Code


  While (@P_Code is not null)
  Begin
  if (@acc_level > 1)
  begin
    If (@NEW_Bedeh > 0) and (@FlagInsert = 0)
       if (select Count(Acc_Code) From dbo.SanadStandard Where Acc_Code=@P_Code and Type='0' AND Id_Sanad = @Id_Sanad  ) > 0 
          Update dbo.SanadStandard set Bedeh = Bedeh  + @NEW_Bedeh , Bestan=0
          where Acc_code = @P_Code and Type='0' AND Id_Sanad = @Id_Sanad 
      Else
         Insert Into    dbo.SanadStandard(Id_sanad,note,Acc_Code,Bedeh,Bestan,Acc_level,Type)
                                             Values (@Id_Sanad,@Acc_Name,@P_Code,@NEW_Bedeh,@New_Bestan,@Acc_Level,'0')
    If (@NEW_Bestan > 0) and (@FlagInsert = 0)
       if (select Count(Acc_Code) From dbo.SanadStandard   Where Acc_Code=@P_Code  and Type='1' AND Id_Sanad = @Id_Sanad )> 0 
          Update dbo.SanadStandard set Bedeh =0 , Bestan=Bestan+@New_Bestan
          where Acc_code = @P_Code and Type='1' AND Id_Sanad = @Id_Sanad
      Else
         Insert Into    dbo.SanadStandard(Id_Sanad,note,Acc_Code,Bedeh,Bestan,Acc_Level,Type)
                                             Values (@Id_Sanad,@Acc_Name,@P_Code,@NEW_Bedeh,@New_Bestan,@Acc_Level,'1')
   end
   
    Select @P_Code = Dbo.GetParent(@P_Code)    
    Select @Acc_Name = Acc_Name From Account Where Acc_Code=@P_Code
    Select @Acc_Level = Acc_Level From Account Where Acc_Code=@P_Code
  END
    FETCH NEXT FROM CursorFetch INTO @Id_Sanad
  END
  CLOSE CursorFetch
  DEALLOCATE CursorFetch


