CODEWITHSUNDEEP
sqlsql-servertriggerstry-catch
Mr_Hmp
Mr_Hmp

Reputation: 2535

Try catch in trigger not suppressing error

I have a trigger and I want to surround the dml statements in the trigger by a try catch block so that any exception that occur in the trigger does not throw any exceptions outside the trigger. But the error is not suppressed.

My trigger is :

ALTER TRIGGER [dbo].[Deal.OnInsertUpdateAddDealAuditDetails]
ON [dbo].[Deal]
AFTER UPDATE, INSERT, DELETE
AS 
BEGIN
    BEGIN TRY
        --SOME DML STATEMENTS
        select 1/0
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

The error output is:

Msg 3616, Level 16, State 1, Line 1
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

Upvotes: 1

Views: 1881

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93764

XACT_ABORT is implicitly ON inside triggers. It rolls back the current transaction when a Transact-SQL statement raises a run-time error.

You have to handle the exception in original Insert query to aboid throwing the error. 

BEGIN TRY
    INSERT INTO deal(col1,col2,..)
    VALUES      (val1,val2,..)
END TRY

BEGIN CATCH
    SELECT Error_number()  AS ErrorNumber,
           Error_message() AS ErrorMessage;
END CATCH

Note : The inserted records will not be present in the table. If you want the inserted records to be present in table though the trigger failed then you may have to commit the transaction first inside the trigger

Upvotes: 2

Related Questions