I have case that some table row is deleted mysteriously. So I created a delete trigger to get hints what really happen.

I found that a stored procedure to delete is executed. This delete stored procedure has three consecutive transactions, but only delete shipping detail table, update barcode status and barcode log not executed.

I don't know how it partially committed or failed to rollback.

Here is the delete stored procedure:

ALTER PROCEDURE [dbo].[spSLSdelShippingDetail]
    @DetailInternalID int,
    @BarcodeInternalID int ,  
    @UserModified varchar(100)
AS
BEGIN
    DECLARE @Issued bit
    DECLARE @IssuedTo int
    DECLARE @InternalID int 
    DECLARE @BarcodeWarehouse int
    DECLARE @ShippingWarehouse int

    DECLARE @IssuedShipping varchar(50) = (SELECT InternalID 
                                           FROM dbo.M_IssuedTo 
                                           WHERE [Description] = 'Shipping')
    DECLARE @InvInternalID int = (SELECT InvInternalID 
                                  FROM T_InventoryBarcode 
                                  WHERE InternalID = @BarcodeInternalID)  
  
    --Get Header Warehouse
    SELECT @ShippingWarehouse = WHInternalID 
    FROM T_ShippingHeader 
    WHERE InternalID = (SELECT ShippingInternalID 
                        FROM T_ShippingDetail 
                        WHERE DetailInternalID = @DetailInternalID)

    --Get Issued, IssuedTo, and Warehouse (Barcode)
    SELECT 
        @InternalID = TBH1.InternalID,
        @Issued = TBH1.Issued,
        @IssuedTo = TBH1.IssuedToInternalID,
        @BarcodeWarehouse = IVB.WHInternalID 
    FROM 
        T_BarcodeHistory AS TBH1
    JOIN 
        T_InventoryBarcode AS IVB ON TBH1.BarcodeInternalID = IVB.InternalID  
    WHERE 
        TBH1.BarcodeInternalID = @BarcodeInternalID 
        AND TBH1.InternalID = (SELECT MAX(TBH2.InternalID) 
                               FROM T_BarcodeHistory AS TBH2 
                               WHERE TBH2.BarcodeInternalID = TBH1.BarcodeInternalID)

    IF @Issued = 1 
       AND @IssuedTo = @IssuedShipping 
       AND @BarcodeWarehouse = @ShippingWarehouse  
    BEGIN
        BEGIN TRY
        BEGIN TRANSACTION [delete];

            DECLARE @ROWCOUNT int = 0

            DELETE FROM T_ShippingDetail
            WHERE DetailInternalID = @DetailInternalID

            SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT

            UPDATE T_BarcodeHistory
            SET Issued = 0,
                IssuedToInternalID = IssuedToInternalIDBefore,
                UserModified = ISNULL(@UserModified, ''),
                dtModified = GETDATE()   
            WHERE InternalID = @InternalID 

            SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT
 
            INSERT INTO T_LogBarcode (BarcodeInternalID, InvInternalID, Tanggal, Remark, Status, UserName, dtRecord, UserModified, dtModified, rowguid)  
            VALUES (@BarcodeInternalID, @InvInternalID, GETDATE(), 'Shipping', 'Delete', @UserModified, GETDATE(), @UserModified, GETDATE(), NEWID())  

            SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT
            
            DECLARE @M varchar(50) = 'Unexpected error!' + CAST(@ROWCOUNT AS varchar(50))

            IF (@ROWCOUNT <> 3) 
                 RAISERROR (@m, 11, 1);  

            COMMIT TRANSACTION [delete];
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            THROW;  
        END CATCH;
    END
    ELSE  
    BEGIN  
        DECLARE @BarcodeID varchar(50) = (SELECT BarcodeID 
                                          FROM T_InventoryBarcode 
                                          WHERE InternalID = @BarcodeInternalID)  

        DECLARE @Msg varchar(max) = 'Delete is not allowed! BarcodeID ''[Barcode]'' is already used for another process!' 
        SET @Msg = REPLACE(@Msg, '[Barcode]', @BarcodeID)  
        RAISERROR (@Msg, 11, 1);  
    END 
END

And here is the AFTER DELETE TRIGGER

ALTER TRIGGER [dbo].[T_ShippingDetail_Delete]
   ON  [dbo].[T_ShippingDetail]
   AFTER Delete
AS 
BEGIN
DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

    CREATE TABLE #inputbuffer 
    (
      EventType nvarchar(30), 
      Parameters int, 
      EventInfo nvarchar(max)
    )

    SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

    INSERT INTO #inputbuffer 
    EXEC (@ExecStr)

    SET @Qry = (SELECT EventInfo FROM #inputbuffer)

    INSERT INTO T_TransactionLog(TransactionType,DetailInternalID,ReferenceInternalID,BarcodeInternalID,Qty,UoM,QtyPack,UoMPack,
     Price,Price2,Disc,SubTotal,ValueAddedTax,IncomeTaxID,IncomeTaxPercentage,IncomeTax,Remarks,CropYear,UserName,dtRecord,
     UserModified,dtModified,dtDeleted,rowguid,InternalNote)
    SELECT 'T_ShippingDetail',DetailInternalID,ShippingInternalID,BarcodeInternalID,NULL,UoM,NULL,NULL,NULL,NULL,NULL,NULL,
     NULL,NULL,NULL,NULL,Remarks,CropYear,UserName,dtRecord,UserModified,dtModified,
     GETDATE(),rowguid,'ShippingInternalID :' + cast(shippingInternalID as varchar(50)) +
     ' - NT LOGIN NAME ' + SUSER_NAME() +' '+ ISNULL(@Qry, '')

    FROM DELETED

END

Can anyone help me to show what really happened?

Thank you.