Trigger error: the current transaction cannot be committed and cannot support operations that are written to the log file

Question

So, I get the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get an error message:

The current transaction cannot be committed and cannot support operations that write to the log file. 

Any ideas on what I'm doing wrong? (this is just a sample that I created to simulate a problem, so please, "why are you doing this?", "it has security implications", etc.)

---

So my table looks like this:

 CREATE TABLE tSOMETABLE ( RecID INT NOT NULL IDENTITY(1,1) Val VARCHAR(20), CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED ( RecID ASC ) ) 

So, in my trigger, I:

 CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT] ON [dbo].[tSOMETABLE] FOR INSERT AS SET NOCOUNT ON BEGIN BEGIN SELECT * INTO #temp FROM INSERTED WHILE EXISTS (SELECT 1 FROM #temp) BEGIN DECLARE @RecID INT SELECT @RecID = RecID FROM #temp t EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID DELETE #temp WHERE @RecID = RecID END END END 

Now sp_SomeProc code looks like this:

 CREATE PROC sp_SomeProc ( @EventType VARCHAR(50), @RecID INT, @Debug BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @ProcTable TABLE ( RecID INT NOT NULL IDENTITY(1,1), Cmd VARCHAR(MAX) ) INSERT INTO @ProcTable(Cmd) SELECT 'EXEC sp_who' UNION SELECT 'EXEC sp_SomeStoredProcThatDoesntExist' DECLARE @RecID INT SELECT @RecID = MIN(RecID) FROM @ProcTable WHILE @RecID IS NOT NULL BEGIN DECLARE @sql VARCHAR(MAX) SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID IF @Debug = 1 PRINT @sql ELSE BEGIN BEGIN TRY EXEC(@sql) END TRY BEGIN CATCH DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX) SELECT @Msg = 'Failed While Executing: ' + @sql SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE() -- DO SOME MORE STUFF HERE AND THEN ... RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH END SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID > @RecID END END 

So, to check, I try:

 INSERT INTO tSOMETABLE(Val) SELECT 'Hello' 

Answer 1

This error occurs if you use the try / catch block inside a transaction. Consider a trivial example:

 SET XACT_ABORT ON IF object_id('tempdb..#t') IS NOT NULL DROP TABLE #t CREATE TABLE #t (i INT NOT NULL PRIMARY KEY) BEGIN TRAN INSERT INTO #t (i) VALUES (1) INSERT INTO #t (i) VALUES (2) INSERT INTO #t (i) VALUES (3) INSERT INTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batch INSERT INTO #t (i) VALUES (4) COMMIT TRAN SELECT * FROM #t 

When the fourth insertion causes an error, the batch is aborted and the transaction is rolled back. So far no surprises.

Now try to handle this error with the TRY / CATCH block:

 SET XACT_ABORT ON IF object_id('tempdb..#t') IS NOT NULL DROP TABLE #t CREATE TABLE #t (i INT NOT NULL PRIMARY KEY) BEGIN TRAN INSERT INTO #t (i) VALUES (1) INSERT INTO #t (i) VALUES (2) BEGIN TRY INSERT INTO #t (i) VALUES (3) INSERT INTO #t (i) VALUES (1) -- dup key error END TRY BEGIN CATCH SELECT ERROR_MESSAGE() END CATCH INSERT INTO #t (i) VALUES (4) /* Error the Current Transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. */ COMMIT TRAN SELECT * FROM #t 

We caught a duplicate key error, but otherwise we are no better. Our party is still ending, and our transaction is still rolling back. The reason is actually very simple:

TRY / CATCH blocks do not affect transactions.

Due to the fact that XACT_ABORT ON, at the time of the occurrence of a duplicate key error, the transaction is doomed. That was done. He was mortally wounded. It pierced the heart ... and the fault is to blame. TRY / CATCH gives SQL Server ... a bad name. (sorry, could not resist)

In other words, it will NEVER commit and will <Back> ALWAYS cancel. The whole TRY / CATCH unit can do is break the body fall. We can use the XACT_STATE () function to find out if our transaction is required. If this is not the case, the only option is to roll back the transaction.

 SET XACT_ABORT ON -- Try with it OFF as well. IF object_id('tempdb..#t') IS NOT NULL DROP TABLE #t CREATE TABLE #t (i INT NOT NULL PRIMARY KEY) BEGIN TRAN INSERT INTO #t (i) VALUES (1) INSERT INTO #t (i) VALUES (2) SAVE TRANSACTION Save1 BEGIN TRY INSERT INTO #t (i) VALUES (3) INSERT INTO #t (i) VALUES (1) -- dup key error END TRY BEGIN CATCH SELECT ERROR_MESSAGE() IF XACT_STATE() = -1 -- Transaction is doomed, Rollback everything. ROLLBACK TRAN IF XACT_STATE() = 1 --Transaction is commitable, we can rollback to a save point ROLLBACK TRAN Save1 END CATCH INSERT INTO #t (i) VALUES (4) IF @@TRANCOUNT > 0 COMMIT TRAN SELECT * FROM #t 

Triggers are always executed in the context of a transaction, so if you can avoid using TRY / CATCH inside them, everything is much simpler.

To solve your problem, CLR Stored Proc can connect to SQL Server in a separate connection to run dynamic SQL. You get the ability to execute code in a new transaction, and the error handling logic is easy to write and easy to understand in C #.