Can we commit the transaction when the sql server trigger fails?

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?

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 `DEC

StackExchange Database Administrators Q#137305, answer score: 11