事务在触发器中结束 批处理已中止 [英] The Transaction Ended In The Trigger The Batch Has Been Aborted

问题描述

我正在使用 Sql Server 2008.我有一个 Trigger 更新我的另外两个表.我已阅读此链接中的堆栈溢出输入链接描述在这里，但它不能完全满足我的需求.下面是我的 Trigger

I am using Sql Server 2008. I have a Trigger which updates my two other tables. I have read the Stack over flow this link enter link description here, but it does not full fill my needs. Below is my Trigger

ALTER TRIGGER [Inventory].[StockUpdationOnIssue]
ON              [Inventory].[StockIssueDetails]
AFTER           INSERT  
AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
                    INSERT INTO TableA 
                        (col1, col2,col3
                        )
                    SELECT      I.col1,I.col2,si.col3
                    FROM        inserted I
                    INNER JOIN  Inventory.StockIssue SI
                    ON          SI.StockIssueId = I.StockIssueId

                    INSERT INTO TableB
                        (col1, col2,col3
                        )
                    SELECT      I.col1,I.col2,si.col3
                    FROM        inserted I
                    INNER JOIN  Inventory.StockIssue SI
                    ON          SI.StockIssueId = I.StockIssueId

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
        RollBack Tran;
    END CATCH
END

以下错误显示给我...

Below error is shown to me...

推荐答案

您可以将错误详细信息保存在错误日志表中，以便您稍后回来进行调查.
类似的东西

You can save the error details in a Error Log table, so you can come back later to investigate.
Something like

ALTER TRIGGER [Inventory].[StockUpdationOnIssue]
ON              [Inventory].[StockIssueDetails]
AFTER           INSERT  
AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
                    INSERT INTO TableA 
                        (col1, col2,col3
                        )
                    SELECT      I.col1,I.col2,si.col3
                    FROM        inserted I
                    INNER JOIN  Inventory.StockIssue SI
                    ON          SI.StockIssueId = I.StockIssueId

                    INSERT INTO TableB
                        (col1, col2,col3
                        )
                    SELECT      I.col1,I.col2,si.col3
                    FROM        inserted I
                    INNER JOIN  Inventory.StockIssue SI
                    ON          SI.StockIssueId = I.StockIssueId

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, @ErrorLine INT 

        SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorProc = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE();
        RollBack Tran;

        INSERT INTO ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
        VALUES               (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)
    END CATCH
END

被表ErrorLog喜欢:

Being table ErrorLog like:

CREATE TABLE ErrorLog 
(
   ErrorLogID INT IDENTITY(1,1),
   ErrorDate DATETIME DEFAULT (GETUTCDATE()),
   ErrorMsg VARCHAR(MAX), 
   ErrorNumber INT, 
   ErrorProc sysname, 
   ErrorLine INT 
)

这篇关于事务在触发器中结束 批处理已中止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！