How to Ignoring errors in Trigger and Perform respective operation in MS SQL Server

Question

I have created AFTER INSERT TRIGGER

Now if any case if an error occurs while executing Trigger. It should not effect Insert Operation on Triggered table.

In One word if any ERROR occurs in trigger it should Ignore it.

As I have used

BEGIN TRY

END TRY
BEGIN CATCH

END CATCH

But it give following error message and Rolled back Insert operation on Triggered table

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

Answer 1

Interesting problem. By default, triggers are designed that if they fail, they rollback the command that fired it. So whenever trigger is executing there is an active transaction, whatever there was an explicit BEGIN TRANSACTION or not on the outside. And also BEGIN/TRY inside trigger will not work. Your best practice would be not to write any code in trigger that could possibly fail - unless it is desired to also fail the firing statement.

In this situation, to suppress this behavior, there are some workarounds.

Option A (the ugly way):

Since transaction is active at the beginning of trigger, you can just COMMIT it and continue with your trigger commands:

CREATE TRIGGER tgTest1 ON Test1 AFTER INSERT
AS
BEGIN
COMMIT;
... do whatever trigger does
END;

Note that if there is an error in trigger code this will still produce the error message, but data in Test1 table are safely inserted.

Option B (also ugly):

You can move your code from trigger to stored procedure. Then call that stored procedure from Wrapper SP that implements BEGIN/TRY and at the end - call Wrapper SP from trigger. This might be a bit tricky to move data from INSERTED table around if needed in the logic (which is in SP now) - probably using some temp tables.

SQLFiddle DEMO

Answer 2

You cannot, and any attempt to solve it is snake oil. No amount of TRY/CATCH or @@ERROR check will work around the fundamental issue.

If you want to use the tightly coupling of a trigger then you must buy into the lower availability induced by the coupling.

If you want to preserve the availability (ie. have the INSERT succeed) then you must give up coupling (remove the trigger). You must do all the processing you were planning to do in the trigger in a separate transaction that starts after your INSERT committed. A SQL Agent job that polls the table for newly inserted rows, an Service Broker launched procedure or even an application layer step are all going to fit the bill.

Answer 3

The accepted answer's option A gave me the following error: "The transaction ended in the trigger. The batch has been aborted.". I circumvented the problem by using the SQL below.

CREATE TRIGGER tgTest1 ON Test1 AFTER INSERT
AS
BEGIN
    SET XACT_ABORT OFF
    BEGIN TRY  
        SELECT [Column1] INTO #TableInserted FROM [inserted]
        EXECUTE sp_executesql N'INSERT INTO [Table]([Column1]) SELECT [Column1] FROM #TableInserted'
    END TRY  
    BEGIN CATCH
    END CATCH
    SET XACT_ABORT ON
END