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!