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

Phenice

I think you forgot to ask a question – Nonperishable

That's still not a question. What is your problem? What do you not know how to do? – Nonperishable

I don't know whats this error means.?? how to handle this error.. – Phenice

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.) – Nonperishable

But why it hits RollBack Tran everything is fine, how to handle this type of situation. Any other way to handle it. – Phenice

One of the inserts fails with some constraint error and catch section gets executed. Put print error_message() before rollback to see the error. – Womanlike

Answer 1

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 
)

Balough

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." – Bridwell

My two cents - SQL Server needs a "LogThisNoMatterWhat" function, and "CatchThisNoMatterWhat" functionality. – Savarin

Answer 2

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

Anfractuous