Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Rather than using a cursor you could use select top 1 * from inserted. There is only ever one record in this resultset so the cursor is superfulous. This sort of problem is why triggers are avoided. Rewrite in a stored procedure = simpler to manage and debug. –  sa555 Sep 5 '13 at 10:08
    
Hi @sa555 and thank you for the comment. I will run some tests and let you know the outcome. –  Hani El Mouallem Sep 5 '13 at 10:53
2  
@sa555: Use of cursors aside, that is absolutely not true for SQL Server (note: this behaviour can be different in other RDBMSes). In fact, assuming the inserted and deleted tables always contain 1 row is the cause of many, many issues. Triggers should always be coded to handle multiple rows in these pseudotables, except in very specific cases. –  Jon Seigel Sep 5 '13 at 13:30
add comment

1 Answer 1

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.

share|improve this answer
    
just got through the article, seems that 1 of the possible issues is the presence of XACT_ABORT in ON State. I'm a bit confused, trying to run this on my test environment, running the same code on more than a couple of hundreds of records and for several times, no errors are being raised and all is okady but on the production server this is another issue :S –  Hani El Mouallem Sep 5 '13 at 14:06
    
just to recap your answer, there is nothing wrong in my code? can this error be based on another trigger on the same table? like another AfterUpdate trigger? or maybe an AfterInsert or AfterDelete? –  Hani El Mouallem Sep 5 '13 at 14:11
    
Your code looks reasonable to me. (I would replace the cursor with a single statement that processes ALL row in inserted. Triggers are not a good place to use 'slow' code like a cursor.) However, why the irregular errors? Based on Balmukund's comments, one possibility could be that if PR_LENGTH1, PR_WIDTH1, or PR_HEIGHT1 are character strings, then an entry error could introduce a conversion problem. –  RLF Sep 5 '13 at 14:34
    
Regarding another trigger: I do not know, but all the triggers would be running under whatever transaction already existed. I would expect that s doomed transaction is doomed no matter how many triggers are running. –  RLF Sep 5 '13 at 14:36
    
Trying to merge the 2 triggers now (both have the same type) and will revert back with the outcome. It could be that the other trigger is causing this issue as they both have the same type. did not risk to change the order of execution of both triggers but will try to merge them now and check the outcome. –  Hani El Mouallem Sep 9 '13 at 10:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.