SQL Server Commit
SQL Server Commit is used for permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit. 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.
