2

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
jma73
  • 91
  • 2
  • 7
  • nesting transactions like this seems like a bad idea. The transaction should have a single purpose. if the purpose fails than and error should generate identifying the conditions for the failure. fixing the data then trying again is a bad idea. the application never is notified of the bad data causing failure. – Golden Lion Aug 02 '21 at 21:11

1 Answers1

0

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)

Angel M.
  • 1,360
  • 8
  • 17
  • 1
    Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible? – jma73 Jan 03 '19 at 15:37
  • Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set – Angel M. Jan 03 '19 at 15:39