ALTER TRIGGER [inv].[Trig_AU_InventoryStock]
ON [inv].[InventoryStock]
after UPDATE
AS
    DECLARE trig_cursor CURSOR FOR
      SELECT [CalculateDate],
             [activitytypeid],
             Cast([number]AS NVARCHAR(50)),
             inserted.[id],
             [warehouseid],
             inserted.[companyid],
             [financialyearid],
             [status]
      FROM   inserted
             INNER JOIN [inv].[recordinventory] RI
                     ON RI.id = inserted.[recordinventoryid]
    DECLARE @TransDate       DATE,
            @ActivtyTypeId   BIGINT,
            @strRef          NVARCHAR(50),
            @RefId           BIGINT,
            @WarehouseId     BIGINT,
            @CompanyId       BIGINT,
            @FinancialYearId BIGINT,
            --@CurrencyId      BIGINT,
            --@Rate            DECIMAL ,
            @Status          BIT

    OPEN trig_cursor;

    FETCH next FROM trig_cursor INTO @TransDate, @ActivtyTypeId, @strRef, @RefId
    , @WarehouseId, @CompanyId, @FinancialYearId, @Status

    WHILE @@FETCH_STATUS = 0
      BEGIN
          DECLARE @oldWarehouseId BIGINT
          --select warehouse from the previous inserted item transaction before updating it
          DECLARE @ItemTransactionId BIGINT
          --select @ItemTransactionId from the previous inserted item transaction before updating it
          DECLARE @OldTransactionDate DATETIME

          --select @OldTransactionDate from the previous inserted item transaction before updating it
          SELECT @oldWarehouseId = it.[warehouseid],
                 @ItemTransactionId = it.id,
                 @OldTransactionDate = it.transactiondate
          FROM   [inv].[itemtransaction] it
                 INNER JOIN inserted
                         ON it.[refid] = inserted.id
                            AND it.[activitytypeid] = inserted.[activitytypeid]
                            AND inserted.[companyid] = it.[companyid]
                 INNER JOIN [inv].[recordinventory] RI
                         ON RI.id = inserted.[recordinventoryid]
                            AND RI.[financialyearid] = it.[financialyearid]

          DECLARE @TransIDIfExists BIGINT
          --check if this record has been inserted as a transaction before or not (avoid error of : can't insert null into itemtransactionID)

          SELECT @TransIDIfExists = it.id
          FROM   inv.itemtransaction it
          WHERE  it.[transactiondate] = @TransDate
                 AND it.[activitytypeid] = @ActivtyTypeId
                 AND it.[refrancenumber] = @strRef
                 AND it.[refid] = @RefId
                 AND it.[companyid] = @CompanyId
                 AND it.[financialyearid] = @FinancialYearId

          IF @Status = 1
            BEGIN
                IF @TransIDIfExists IS NOT NULL --exists
                  BEGIN
                      IF ( @oldWarehouseId <> @WarehouseId )
                        --warehouse has been changed
                        BEGIN
                            DECLARE @Firsttime BIT
                            SET @Firsttime = 1
							  --update old warehouse in itemledger
							  --delete all trans det of the old warehouse and recalculate ledger for old
							  EXEC [dbo].[Process_recalculate_itemledgerwhenchangewarehouse]
								@RefId,
								@TransDate,
								@ActivtyTypeId,
								@oldWarehouseId,
								@CompanyId,
								@FinancialYearId,
								@ItemTransactionId,
								@Firsttime,
								NULL,--@IsItemTransfer
								NULL;--@IsDebitWarehouse
                            --here we removed the last itemtransaction detail and recalculated the old warehouse
                            --insert into itemtransaction detail case update warehouse and not update any of sale or puchase detail's item
                            EXEC [dbo].[Process_update_itemtransaction]
                              @TransDate,
                              @ActivtyTypeId,
                              @strRef,
                              @RefId,
                              @WarehouseId,
                              @CompanyId,
                              @FinancialYearId,
                              NULL,
                              NULL;
					END
						 ELSE --@oldWarehouseId = @WarehouseId
							BEGIN
								EXEC [dbo].[Process_update_itemtransaction]
								  @TransDate,
								  @ActivtyTypeId,
								  @strRef,
								  @RefId,
								  @WarehouseId,
								  @CompanyId,
								  @FinancialYearId,
								  NULL,
								  NULL;
							END
				  END
                ELSE IF @TransIDIfExists IS NULL--the transaction doesn't exist
                  BEGIN
                      EXEC [dbo].[Process_insert_itemtransaction]
                        @TransDate,
                        @ActivtyTypeId,
                        @strRef,
                        @RefId,
                        @WarehouseId,
                        @CompanyId,
                        @FinancialYearId,
                        NULL,
                        NULL;
					  END



					 EXEC [dbo].[Process_insert_itemtransactiondetailcasenotupdated]
								@RefId,
								@TransDate,
								@ActivtyTypeId,
								@WarehouseId,
								@CompanyId,
								@FinancialYearId,
								@ItemTransactionId,
								0

            END
          ELSE --@Status = False
            BEGIN
                IF @TransIDIfExists IS NOT NULL--the transaction exist
                  BEGIN
                      --delete this trans and its detail because the status is false
                      DECLARE @ItemId7 BIGINT
                      DECLARE trig_cursor7 CURSOR FOR
                        SELECT [itemid]
                        FROM   inserted
                               INNER JOIN [inv].[inventorystockdetails] isd
                                       ON inserted.id = isd.[inventorystockid]
                        WHERE  isd.[inventorystockid] = inserted.id

                      OPEN trig_cursor7

                      FETCH next FROM trig_cursor7 INTO @ItemId7

                      WHILE @@FETCH_STATUS = 0
                        BEGIN
                            EXEC [dbo].[Process_delete_itemtransactiondetail]
                              @ItemId7,
                              @ActivtyTypeId,
                              @RefId,
                              NULL,
                              NULL--delete details first
                            EXEC [dbo].[Process_delete_itemtransaction]
                              @ActivtyTypeId,
                              @RefId,
                              @CompanyId,
                              @FinancialYearId,
                              NULL

                            FETCH next FROM trig_cursor7 INTO @ItemId7
                        END

                      CLOSE trig_cursor7

                      DEALLOCATE trig_cursor7
                  END
            END

          FETCH next FROM trig_cursor INTO @TransDate, @ActivtyTypeId, @strRef,
          @RefId
          , @WarehouseId, @CompanyId, @FinancialYearId, @Status
      END;

    CLOSE trig_cursor;

    DEALLOCATE trig_cursor;