CODEWITHSUNDEEP
t-sqltransactionsrollbacknested-transactions
jma73
jma73

Reputation: 101

Rollback nested transaction and log error - in Trigger, Sql Server 2008

I have a trigger (that may modify a value on the insert) on insert statements to a table. If an error occurs in the trigger, I want to log it. And the INSERT must still be inserted. So I use a TRY / CATCH block, where the catch part, will do the logging. The problem is that I can only rollback the whole transaction and then log. But then the insert is also rolled back.

Running on Sql Server 2008...

So far so good.

To test the logging in the catch part, I am using RAISERROR. (Maybe that is a problem? I will return to that part). Then I am running the insert to the table.

In the trigger I have placed a BEGIN TRANSACTION triggerTransaction2. I named it, so I could rollback that specific transaction. Note also the insert statement has started a transaction. But the problem is that I cannot rollback the nested (triggerTransaction2) transaction. Only the whole transaction (using ROLLBACK without a name).

If I look at XACT_STATE() then it is -1. Which should mean that "it can only request a full rollback of the transaction.".

I have tried to simplify the code, and hope it still makes sense. Also I have followed this example - and that works for me, but is is also not with a nested transaction.

So I am thinking, either is it not good to use RAISERROR with the selected values.

Pseudocode for the trigger:

CREATE TRIGGER [dbo].[Trigger_SomeTableStatus]
ON [dbo].[SomeTable]
FOR INSERT
AS

BEGIN
    SET NoCount ON
    -- declaring som variables;         DECLARE @isActive BIT;

    BEGIN TRY

        BEGIN TRANSACTION triggerTransaction2       
        -- SAVE TRANSACTION triggerTransaction2

        -- some logic ... 
        RAISERROR(' Test error message; *failed* ', 16, 1);

        COMMIT TRANSACTION triggerTransaction2

    END TRY
    BEGIN CATCH

        -- HERE  XACT_STATE() = -1)
        ROLLBACK TRANSACTION   triggerTransaction2      -- Cannot rollback with the name 

        BEGIN TRY
            BEGIN TRANSACTION triggerTransactionLog
            -- Do the logging..
            COMMIT TRANSACTION triggerTransactionLog
        END TRY
        BEGIN CATCH
        --      -- Logging of error failed...
        END CATCH
    END CATCH
END

Upvotes: 2

Views: 1124

Answers (1)

Angel M.
Angel M.

Reputation: 1358

Yes, this is because when you make a ROLLBACK the full transaction is cancelled. You have to create a table in memory like:

DECLARE @Log AS TABLE 
(
    Description NVARCHAR(100)
);

Then, insert what you want to log in the @Log table. Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)

Upvotes: 0

Related Questions