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

try catch in trigger sql server
the transaction ended in the trigger. the batch has been aborted. sql server 2012
sql error 3609 sqlstate s0001
xact_abort
xact_abort raiserror
sql server auto rollback
sql abort on error
set xact_abort off trigger

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.


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

CREATE TRIGGER (Transact-SQL), How do you create a trigger delete in SQL Server? A SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance.


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.

SQL Server TRY CATCH, Consult this MSSQL error code list to find explanations for error The timeout period elapsed prior to completion of the operation or the server is not responding. 287, 16, No, The %ls statement is not allowed within a trigger. ls' is not allowed in the OUTPUT clause, because it performs user or system  Using UPDATE and COLUMNS_UPDATED for SQL Server Triggers. SQL Server provides 2 functions to help determine which columns were involved in a statement that caused a trigger to fire. They are UPDATE and COLUMNS_UPDATED. UPDATE accepts a single parameter which is a column name for the underlying table.


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

SQL Server : trigger FOR DELETE, Using explicit transactions in SQL Server isn't like sprinkling magic dust, DML (​Data Manipulation Language) statements that trigger an error will be rolled back. In this batch, we execute all the insertions in separate statements, will be that corresponding to the last statement executed in the trigger,  Server Scoped SQL Server DDL Triggers. In case we want to see DDL triggers that affect the entire server we need to look at the Server Objects folder in the server tree view. You will see a child branch Triggers. Expand the Triggers folder to see a list of server scoped DDL triggers.


Use the inserted and deleted Tables, The error handling of SQL Server has always been somewhat mysterious. Since the release of SQL Server 2005, you've been able to handle errors in your T-SQL the SalesLastYear value is updated and the operation is completed, The name of the stored procedure or trigger that generated the error. A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.


Database engine events and errors, Procedure – If the error occurred in a stored procedure, trigger or You don't get this particular error in SQLCMD or in the old Query Analyzer That is, if you perform a modifying operation, SQL Server starts a Exactly what implications this has for our error and transaction handling, we ignore for now, but  When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider).


Handling Constraint Violations and Errors in SQL Server, It also covered what actions SQL Server can take in case of an error, and you learnt that there DDL respectively and I introduce the CMD Catch Handler, an extension of SqlEventLog Constraint-like validations are often performed in triggers. The Products element is ignored when Operation is Delete. If the temporary table exists, the trigger should know to end and not perform the actions. In the update statement you want to perform, create the temporary table first. It will be seen in the same transaction as the trigger and it will cause the trigger to ignore your statement.