Edit Question - KnowledgeHub

Title Body

I have a stored procedure which is called inside a trigger on Insert/Update/Delete. The problem is that there is a certain code block inside this SP which is not critical. Hence I want to ignore any erros arising from this code block. I inserted this code block inside a TRY CATCH block. But to my surprise I got the following error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Then I tried using SAVE & ROLLBACK TRANSACTION along with TRY CATCH, that too failed with the following error: The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction. My server version is: Microsoft SQL Server 2008 (SP2) - 10.0.4279.0 (X64) Sample DDL: IF OBJECT_ID('TestTrigger') IS NOT NULL DROP TRIGGER TestTrigger GO IF OBJECT_ID('TestProcedure') IS NOT NULL DROP PROCEDURE TestProcedure GO IF OBJECT_ID('TestTable') IS NOT NULL DROP TABLE TestTable GO CREATE TABLE TestTable (Data VARCHAR(20)) GO CREATE PROC TestProcedure AS BEGIN SAVE TRANSACTION Fallback BEGIN TRY DECLARE @a INT = 1/0 END TRY BEGIN CATCH ROLLBACK TRANSACTION Fallback END CATCH END GO CREATE TRIGGER TestTrigger ON TestTable FOR INSERT, UPDATE, DELETE AS BEGIN EXEC TestProcedure END GO Code to replicate the error: BEGIN TRANSACTION INSERT INTO TestTable VALUES('data') IF @@ERROR > 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION GO

Tags (comma-separated) Cancel