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;
INSERT INTO dbo.Archive
FROM inserted ins
WHERE NOT EXISTS (SELECT *
FROM dbo.Archive arc
WHERE arc.[Login] = ins.[Login])
-- assuming [Login] field exists and should be unique
DECLARE @DoNothing INT;
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:
SET NOCOUNT ON;
SET XACT_ABORT OFF;
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;
PRINT 'Entering CATCH block...'; -- for debug only
ROLLBACK TRANSACTION InsertSaveHere;
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.