Try/Catch within a trigger to supress errors

• Chris Gierlack

  SSCrazy

  Points: 2326

  More actions

  December 12, 2008 at 11:22 am

  #155019

  I realize that triggers are implicit transactions and any error dooms the transaction but why can't I trap the error in the trigger and let the insert/update/delete finish anyway?

  I'm using the following syntax to do a basic test and it still raises the error and the whole transaction fails....I need the transaction to complete regardless of whether or not the trigger works:

  CREATE TRIGGER [dbo].[trMyTrigger]

  ON [dbo].[myTbl]

  AFTER INSERT, UPDATE

  AS

  SET NOCOUNT ON

  BEGIN TRY

  -- Some code here

  -- Force error

  RAISERROR('Test pass through error',16,1)

  END TRY

  BEGIN CATCH

  DECLARE @Message VARCHAR(MAX)

  SELECT @Message = ERROR_MESSAGE()

  EXEC xp_logevent 60000, @Message, WARNING

  END CATCH

• Mr.Sahand

  Old Hand

  Points: 391

  More actions

  July 22, 2015 at 1:09 am

  #1814013

  Add Set XACT_Abort oFF to the beginning of your trigger. to my knowledge, that is the only thing you can do as a workaround.