The Transaction Ended In The Trigger The Batch Has Been Aborted

Question

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...

enter image description here

That's still not a question. What is your problem? What do you not know how to do? — lc., Jul 30 '13 at 07:54
I don't know whats this error means.?? how to handle this error.. — Shahid Iqbal, Jul 30 '13 at 07:54
It means that the transaction was rolled back inside your trigger so execution stopped. Most likely your `RollBack Tran` statement was hit. (Unless I'm missing something here.) — lc., Jul 30 '13 at 07:56
But why it hits `RollBack Tran` everything is fine, how to handle this type of situation. Any other way to handle it. — Shahid Iqbal, Jul 30 '13 at 07:58
One of the inserts fails with some constraint error and catch section gets executed. Put `print error_message()` before rollback to see the error. — Nikola Markovinović, Jul 30 '13 at 08:16

Luis LL · Accepted Answer · 2013-07-30T08:47:24.210

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

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 was important for me to `ROLLBACK TRANSACTION` in the `CATCH` block, else an error like "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." — Nate Anderson, Sep 28 '16 at 20:31
My two cents - SQL Server needs a "LogThis[NoMatterWhat](https://dba.stackexchange.com/questions/220847)" function, and "CatchThis[NoMatterWhat](https://dba.stackexchange.com/questions/242014)" functionality. — Elaskanator, Aug 20 '19 at 19:33

score 3 · Answer 2 · answered Mar 24 '15 at 17:26

I got the same error message. You don't need transaction within the trigger as it has a transaction by default; i.e. you don't need begin tran nor commit tran. But you could use in the catch the rollback tran and it will rollback in case of exceptions.

ALTER TRIGGER [Inventory].[StockUpdationOnIssue]
ON              [Inventory].[StockIssueDetails]
AFTER           INSERT  
AS
BEGIN
    BEGIN TRY
                    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
    END TRY
    BEGIN CATCH
        RollBack Tran;
    END CATCH
END

The Transaction Ended In The Trigger The Batch Has Been Aborted

2 Answers2

Linked