11

I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing.

When an insert happens on the Customers table, I want to insert the same data into the Archive table. When the trigger fails, I don't want to roll back the entire transaction, which would lead to data loss on the Customers table.

Even if the trigger fails, I want the data to be inserted in the Customers table, and the stored procedure should return the Customer_ID as usual.

ALTER TRIGGER [dbo].[Customer_Insert_Trigger_Test] 
   ON  [dbo].[Customers]
   AFTER INSERT
AS 
BEGIN

BEGIN TRY
    begin transaction;
    set nocount  on;
    SAVE TRANSACTION InsertSaveHere;
    --Simulating error situation
    RAISERROR (N'This is message %s %d.', -- Message text.
       11, -- Severity,
       1, -- State,
       N'number', -- First argument.
       5); -- Second argument.

   Insert into Archive select * from Inserted;
    commit transaction;

  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION InsertSaveHere;
  END CATCH
END

My question is mostly around how to avoid the actual insert on customers table to roll back if the trigger fails. How to change my code for that?

  • Hi there. I realize that it's been almost 4.5 years since this question was asked and answered, but I came across it again recently and thought of an additional option which is probably better than the original two (or at least should be done in conjunction with one of them). Please review the new Option # 1 in my answer, even if only to have it in the back of your head if you ever run into this scenario again. – Solomon Rutzky Oct 21 '20 at 22:35
10

The Question mentions that the "code is failing" but there is no indication of any error message or what specifically is failing. Including at least one, if not both, of those pieces of information always helps get better answers.

For the moment, I see something that appears to be an incorrect assumption about Triggers and Transactions: you increment the @@TRANCOUNT by calling BEGIN TRAN; but only decrement @@TRANCOUNT when there is no error and the COMMIT TRAN; line executes within the TRY block. In the case of an error, the COMMIT is skipped and a ROLLBACK of the Save Point occurs. But rolling-back a Save Point does not decrement @@TRANCOUNT in which case the INSERT operation ends and the Transaction is still active.

Triggers exist within an internally started Transaction that binds it to the DML operation that fired the Trigger. This is how you are able to call ROLLBACK within a Trigger to cancel that DML operation.

Option 1 (prevent the error in the first place — PREFFERED if the error condition can be reliably tested for)

(Added 2020-10-21: Not sure how or why I didn't think of this option when I posted this answer)

If at all possible, if the condition(s) that cause the error can be tested for, then best to test for that condition prior to attempting the operation that sometimes fails. If the error never actually happens, then you don't need to alter default behavior and/or add custom transaction handling. In your case (i.e. adding the new record(s) to the archive table), you might could do one of the following:

SET NOCOUNT ON;

BEGIN TRY

    INSERT INTO dbo.Archive
        SELECT *
        FROM   inserted ins
        WHERE  NOT EXISTS (SELECT *
                           FROM   dbo.Archive arc
                           WHERE  arc.[Login] = ins.[Login])
                 -- assuming [Login] field exists and should be unique
END TRY
BEGIN CATCH
    DECLARE @DoNothing INT;
END CATCH;

There are times when this won't catch everything and there still might be the occasional violation, in which case you might still need to also use Option 2. But, even if using Option 2 and it always works, still best to attempt preventing the error because work had to be done in order to arrive at the condition causing the error, and that is just wasted time, IO, contention, etc.

Option 2A (prevent errors from cancelling the Transaction — PREFERRED if there are multiple DML statements in the Trigger)

With this in mind, you should be able to remove the BEGIN TRAN; and COMMIT TRAN; lines in order to get this working. The net-effect will be that if there is no error, the INSERT into the Archive table will commit as expected, but if there is an error, it will do the ROLLBACK to the Save Point and carry on.

HOWEVER, after removing those two pieces, you are still left with the sticky situation of getting the following error:

Msg 3931, Level 16, State 1, Procedure Customer_Insert_Trigger_Test, Line XXXXX
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

The reason for this behavior seems to be an implicit setting of XACT_ABORT ON by the system as it calls the Trigger. The effect of XACT_ABORT ON is to cancel the Transaction (and the query batch) for most errors (except compilation errors or from RAISERROR). The remedy? Just set XACT_ABORT OFF at the beginning of the Trigger.

For example, the following works for me:

CREATE
--ALTER
TRIGGER [dbo].[Customer_Insert_Trigger_Test] 
   ON  [dbo].[Inserts]
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT OFF;

BEGIN TRY
    PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR(10), @@TRANCOUNT); -- for debug only
    SAVE TRANSACTION InsertSaveHere;

    -- Simulating error situation
    DECLARE @Error INT = 1 / 0; -- runtime error

   -- Insert into Archive select * from Inserted;
END TRY
BEGIN CATCH
    PRINT 'Entering CATCH block...'; -- for debug only
    ROLLBACK TRANSACTION InsertSaveHere;
END CATCH;

END;

Please note that this method does not alter the expected behavior of Triggers on this table with respect to: 1) the actual COMMIT happening at the outer-most layer (either the initial DML statement or beyond that if an explicit Transaction had been started prior to that statement), 2) the ability of other potential Triggers on this table from issuing a ROLLBACK to cancel the operation, and 3) the ability of an explicit Transaction, started prior to the DML statement on this table, from issuing a ROLLBACK to cancel all changes including the DML operation on this table.

Option 2B (prevent errors from cancelling the Transaction — PREFERRED if there is a single DML statement in the Trigger)

Of course, if the only thing that could error here is the INSERT into the Archive table, then you could probably also get rid of the SAVE TRAN and ROLLBACK TRANSACTION InsertSaveHere; and just do something in the CATCH block so that it isn't empty, something like DECLARE @Test INT; might work. The reasoning here is that a single DML statement that errors never really happened, so there is nothing to roll-back ;-).


Option 3 (NOT PREFERRED)

To answer the Question as stated in the title: you should be able to COMMIT within the Trigger, but I would be eXtreeemely cautious about doing such a thing as it alters the expected behavior of when the Transaction will Commit or Rollback, which might prevent proper operation of other Triggers on this table (they won't be able to issue a ROLLBACK to cancel the operation if this Trigger runs first), and would prevent intended operation of an explicit Transaction started prior to the DML operation on this table.

To do this (NOTE: you need to have read the paragraph directly above before continuing to read this paragraph), you would issue a COMMIT TRAN; (since the Trigger already exists within a Transaction), and then execute a BEGIN TRAN;. The COMMIT TRAN; will commit the initial DML operation, and the BEGIN TRAN; will put the @@TRANCOUNT back to 1 so that when the Triggers execution ends, you don't get the error stating that the Trigger ended with a different @@TRANCOUNT than it had when it began.

3

A trigger is already always operating within an implicit transaction, see the related question:

Is there a way to ensure that a SQL Server trigger will be executed?

You can catch an error and prevent an abort/rollback, but you cannot "commit" the transaction from within the trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.