If you rollback in a trigger then everything is rolled back, and you should never do this. If you actually wanted a rollback then you can simply remove the SET XACT_ABORT OFF as well as the TRY CATCH and it will get rolled back.

In this case, you want to swallow the error, so you need to remove the ROLLBACK .

CREATE OR ALTER TRIGGER [dbo].[trigger_error]
ON dbo.trigger_test
AFTER INSERT, UPDATE
AS

SET XACT_ABORT OFF;
SET NOCOUNT ON;

BEGIN TRY
    INSERT INTO dbo.trigger_log (id, message, loaddatetime)
    SELECT 1, 'sdfsd','df';
END TRY
BEGIN CATCH
END CATCH;

Do not select data out of a trigger, it's not properly supported.

TRY with an empty CATCH is an odd thing to do anyway. You should probably consider why you are getting errors and try to avoid them.

In my opinion the trigger you have is overly complicated and flawed.

Firstly I suggest against using ROLLBACK in the TRIGGER . For the above there is no need for it, as when the INSERT statement fails the error generated will be propagated to the outer scope, and that will present the error to the front end. As the trigger failed, then the transaction will also be "automagically" rolled back.

If you need to add logic in your trigger so that in the event of some logic you want the DML statement to fail then THROW a meaningful error, don't ROLLBACK ; this gives the front end a meaningful error and the transaction is still rolled back.

You also have a SELECT 1 in your trigger which isn't part of a assignment/DML operation. Returning a dataset from a trigger is deprecated and should be avoided. I don't know what the goal of the SELECT in the above is, it just SELECT s the int value 1 , so I assume it should simply not be there.

This end up with your DDL statements just being the following:

CREATE TABLE dbo.trigger_log(id int,
                             message varchar(50),
                             loaddatetime datetime2(7)); --Corrected spelling

CREATE TABLE dbo.trigger_test (id int,
                               name varchar(50),
                               status varchar(50));
GO

CREATE OR ALTER TRIGGER [dbo].[trigger_error] ON dbo.trigger_test
AFTER INSERT, UPDATE AS
BEGIN

    INSERT INTO dbo.trigger_log(id, message, loaddatetime)
    VALUES( 1, 'sdfsd','df');
END;
GO

If you then try to INSERT into the table dbo.trigger_test like you did, the following error now occurs:

INSERT INTO dbo.trigger_test (id, name, status)
VALUES (1,'xyz','ok');

Msg 241, Level 16, State 1, Procedure trigger_error, Line 6
Conversion failed when converting date and/or time from character string.

In truth, "swallowing" an error is just not a good idea in any scenario. If the trigger errors, then there's a problem and it should be addressed. In this case, that's because of the incorrect data attempted to be inserted into the table dbo.trigger_log . That should be addressed. For a log table, inserting data into the "main" table and not doing the appropriate logging could be a significant concern so you need to be aware.