Why TRY CATCH does not suppress exception in trigger

Question

I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target database (table) will be up to date to source database (table). I have 3 hundred tables to synchronize. Some of then have different physical data structure. I cannot use standard approaches of Sql Server (replication, DTS...) because of different data schema and other restrictions (time to implement, environment issues...). My goal is: this trigger should NOT impact on INSERT, DELETE, UPDATE of records in a source table. I tried so solution:

CREATE TRIGGER dbo.MyTrigger
...
   AFTER INSERT
....
BEGIN TRY
     --RAISERROR('Test error', 16, 2)
END TRY
BEGIN CATCH
  -- nothing
END CATCH

I have inserted RAISEERROR to simulate error. I hoped, try/catch suppressed that error and record was been inserted (deleted, or updated) successfully. Don't. It does not work. I got error:

An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

Is it possible to realize my solution in this way. How can I catch and hold (suppress) any error in my trigger?

What sense to use try/catch in trigger if it does not work?

Answer 1

The transaction is doomed with pretty much any exception and must be rolled back.

From "Using TRY...CATCH in Transact-SQL" on MSDN

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

This example shows why. XACT_STATE() doesn't register an implied transaction (eg no explicit BEGIN TRAN)

CREATE TABLE TrgTest (gbn int NOT NULL);
GO
CREATE TRIGGER TRG_TrgTEst_I ON TrgTest AFTER INSERT
AS
BEGIN TRY
     SELECT '1', @@TRANCOUNT, XACT_STATE();
     RAISERROR('Test error', 16, 2);
END TRY
BEGIN CATCH
     SELECT '2', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

BEGIN TRANSACTION
INSERT TrgTest VALUES (1)
SELECT 'will not run'
GO
SELECT 'next batch'
GO


BEGIN TRY
    BEGIN TRANSACTION
    SELECT 'a', @@TRANCOUNT, XACT_STATE();
    INSERT TrgTest VALUES (1)
    SELECT 'b', @@TRANCOUNT, XACT_STATE();
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    SELECT 'c', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

DROP TABLE TrgTest;

Answer 2

I found a slightly risky* workaround that suppresses "uncomittable transactions" that occur within a DML trigger.

Caution: It is generally unsafe to suppress transaction errors in a trigger, so think long and hard before you attempt to do this. This method is extremely unsafe if there are other DML triggers on the same table, because you may be committing invalid data that another trigger intends to roll back.

To suppress serious errors that occur within your DML trigger:

1. Commit the implicit pre-existing transaction that represents the initial INSERT/UPDATE/DELETE command
2. Inside of a TRY block, use sp_executesql to attempt your risky business.
3. At the very end of your trigger, begin a new transaction. This will prevent Error 3609: The transaction ended in trigger.