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:
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