none
try catch in trigger giving Msg 3930 The current transaction cannot be committed error RRS feed

  • Question

  • hi everyone,
    I have a trigger that fires on update. In the trigger I check if the input is valid with a function and catch the error from function in catch block and write the error to log file. however when it tries to insert into error log table it gives the below error.

    Msg 3930, Level 16, State 1, Procedure sLogError, Line 22
    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.

    Code:
    Trigger

    BEGIN TRY
    
    	-- CHECK IF THE DATA FOR THAT SERIAL NO IS OK
    
    	EXEC dbo.sCheckSerialNumberInfo @SerialNo 
    
                    -- do some other things
    
    END TRY
    
    BEGIN CATCH
    
    	IF XACT_STATE() = -1 ROLLBACK
    
    	--PRINT XACT_STATE()
    
    	DECLARE @ErrNo INT, @ErrSeverity INT, @ErrState INT, @ErrLine INT
    
    	DECLARE @ErrProcedure NVARCHAR(126)
    
    	DECLARE @ErrMessage NVARCHAR(2048)
    
    	DECLARE @CustomErrMessage NVARCHAR(512)
    
    	SELECT @ErrNo = ERROR_NUMBER(), @ErrSeverity = ERROR_SEVERITY(), @ErrState = ERROR_STATE(), @ErrLine = ERROR_LINE(), @ErrProcedure = ERROR_PROCEDURE(), @ErrMessage = ERROR_MESSAGE()
    
    	SET @CustomErrMessage = 'Error in tblorders_units Update Trigger' 
    
    	EXEC dbo.sLogError @ErrNo, @ErrSeverity, @ErrState, @ErrLine, @ErrProcedure, @ErrMessage, @CustomErrMessage
    
    END CATCH
    
    


    CheckInput function

    -- Declare the return variable here
    
    DECLARE @RetVal INT
    
    -- find RetVal
    
    -- IF THERE IS AN ERROR<br/>	IF @RetVal = 1
    
    	BEGIN
    
    	-- RAISE ERROR SO CATCH IN TRIGGER CAN PROCESS THE ERROR
    
    	RAISERROR ('Gtin, lot number, manufacture date or expiry date for Serial No %s is invalid',
    
    					16,
    
    					1,
    
    					@SerialNo)
    
             END	
    
    -- Return the result of the function
    
    RETURN @RetVal
    
    


    LogError function

    INSERT INTO dbo.ERROR_LOG
    
               ([ErrorNumber]
    
               ,[ErrorSeverity]
    
               ,[ErrorState]
    
               ,[ErrorLine]
    
               ,[ErrorProcedure]
    
               ,[ErrorMessage]
    
               ,[CustomErrorMessage]
    
               ,[ErrorDate])
    
         VALUES
    
               (@ErrNo,
    
                @ErrSeverity,
    
                @ErrState,
    
                @ErrLine,
    
                @ErrProcedure,
    
                @ErrMessage,
    
                @CustomErrMessage,
    
                GETDATE())
    
    
    I know that triggers are executed in transactions and if there is an error I have to rollback. but when i check XACT_STATE in catch block it is 0.
    I also want to know what the effect of a rollback in a trigger is . does it cancel the whole statements called in the trigger.
    Any help is greatly appreciated.
    Regards
    Thursday, June 25, 2009 12:58 PM

Answers

  • well as it turns out that XACT_STATE function does not count the system transactions (like in triggers) so whenever i check it, it is always 0. I can use TRANCOUNT but if i rollback the transaction the whole trigger is cancelled. this is not what i want, i want the trigger to go on from the next statement. since i cannot do this with the current approach, i guess i need to rewrite the code without RAISEEROR.
    i wanted to let people who face the same issue know.
    cheers
    Friday, June 26, 2009 6:55 AM

All replies

  • well as it turns out that XACT_STATE function does not count the system transactions (like in triggers) so whenever i check it, it is always 0. I can use TRANCOUNT but if i rollback the transaction the whole trigger is cancelled. this is not what i want, i want the trigger to go on from the next statement. since i cannot do this with the current approach, i guess i need to rewrite the code without RAISEEROR.
    i wanted to let people who face the same issue know.
    cheers
    Friday, June 26, 2009 6:55 AM
  • This issue had me confused for a few minutes, and Googling for "SQL Server msg 3930" returned this post within the first five results.  Thanks for the quick post, you saved me some time!
    Thursday, December 16, 2010 3:40 PM