Sql-server – Uncommitable T-SQL transaction after trigger applied to table

rollbacksql-server-2012t-sqltransactiontrigger

Good morning,
I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record with a corresponding value. After applying the trigger, everything works perfectly, but at a certain time, I get the following error:

Unexpected error:The current transaction cannot be committed and
cannot support operations that write to the log file. Roll back the
transaction. The statement has been terminated

I found a thread to handle errors using Try/Catch blocks with XACT_ABORT() and XACT_STATE(), but still the same issue is happening. Again, the issue is not occuring all the time, but sometimes only.
Here is my Trigger:

CREATE TRIGGER [dbo].[UTRAfterUpdate] 
   ON  [dbo].[TBL_STOCK] 
   AFTER UPDATE
AS 
BEGIN

    SET NOCOUNT ON;


    DECLARE @CBM FLOAT, @T NVARCHAR(1);
    DECLARE C CURSOR FOR SELECT LEG_PRODUCT_ID, PR_LENGTH1, 
                         PR_WIDTH1, PR_HEIGHT1, PR_TYPE FROM INSERTED
    DECLARE @ID BIGINT, @L INT, @W INT, @H INT, @TYPE NVARCHAR(1)

    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        OPEN C
        FETCH NEXT FROM C INTO @ID, @L, @W, @H, @TYPE
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(UPDATE(PR_LENGTH1) OR UPDATE (PR_WIDTH1) OR UPDATE(PR_HEIGHT1))
            BEGIN
                SET @CBM = (cast(@l AS DECIMAL(18,2))/100
                                       *cast(@w AS DECIMAL(18,2))/100
                                       *cast(@h AS DECIMAL(18,2))/100)
                SET @T = CASE 
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00) 
                          AND (@h <= 170) THEN 'C'
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00)
                          AND (@h >  170 AND @h <= 220) THEN 'V'                       
                    WHEN (@CBM > 13.00 AND @CBM <= 20.00)
                          AND (@h <= 220) THEN 'V'
                    WHEN (@CBM > 0.00  AND @CBM <= 20.00)
                          AND (@h >  220 AND @h <= 250) THEN 'B'                         
                    WHEN (@CBM > 20.00 AND @CBM <= 30.00)
                          AND (@h <= 250) THEN 'B' 
                    WHEN (@CBM > 0.00  AND @CBM <= 30.00)
                          AND (@h >  250) THEN 'T'
                    WHEN (@CBM > 30.00) THEN 'T'
                    ELSE @type
                END
                IF(@type <> @T)
                    UPDATE dbo.TBL_STOCK
                                    SET PR_TYPE = @t,
                                    LAST_ACTION = 'UTR',
                                    LAST_USER = 'System' 
                    FROM TBL_STOCK STOCK INNER JOIN INSERTED INS
                            ON STOCK.LEG_PRODUCT_ID = INS.LEG_PRODUCT_ID
                    WHERE STOCK.LEG_PRODUCT_ID = @id
            END
        FETCH NEXT FROM C INTO @id, @l, @w, @h, @type    
        END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT 'The transaction is in an uncommittable state.'
            + ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT 'The transaction is committable.'
            + ' Committing transaction.'
            COMMIT TRANSACTION;   
        END;
    END CATCH

CLOSE C
DEALLOCATE C
END

Can you please assist me n solving the issue?

Thanks in advance!

Best Answer

I definitely would not say that only 1 row is ever passed into the trigger. Triggers handle every row updated in a single statement. (I see Jon Seigel has made the same point.)

The problem is that inside a trigger an error can occur that results in a state where the transaction cannot be committed. Your main issue will be determining why the transaction became uncommittable.

See http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

In part it says:

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

So, your TRY/CATCH and testing against XACT_STATE is correct. It is just too late to overcome whatever already happened to produce a "doomed" transaction.

Balmukund has a blog post on this topic below. He points out that he cannot define every condition that might cause a doomed transaction, but he offers some hints:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/errors-raised-with-severity-level-16-may-cause-transactions-into-doomed-state.aspx

As per our intensive research we asserted that XACT_STATE changes is not governed by a single rule and depends on the transaction state (user or system initiated and if an active transaction or not) and the type of error being raised. For example, transactions having CONVERT\CAST errors and DDL operations (ALTER TABLE\DATABASE\INDEX etc.) on failure will make the state of the transaction as -1 and will terminate the transaction.

One more example which could get your transaction into doomed state is when you attempt to convert a value that is incompatible with target data type.