none
Trigger - The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    Question

  • Hi,

    I have trigger will do insert in to audit table if any DML like Insert,Update and Delete.

    But Some times the audit table is missing data is nothing but trigger fails.

    So I have added TRY/CATCH to trigger and sending email with errormessage() like this ..

     GO
    /****** Object:  Trigger [dbo].[tr_trigtest_t_TaskMaster]    Script Date: 05/03/2014 15:35:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER TRIGGER [dbo].[tr_trigtest_t_TaskMaster] ON [dbo].[t_TaskMaster]
    FOR INSERT
    	,UPDATE
    	,DELETE
    AS
    DECLARE @bit INT
    	,@field INT
    	,@maxfield INT
    	,@char INT
    	,@fieldname VARCHAR(128)
    	,@TableName VARCHAR(128)
    	,@PKCols VARCHAR(1000)
    	,@sql VARCHAR(2000)
    	,@UpdateDate VARCHAR(21)
    	,@UserName VARCHAR(128)
    	,@Type CHAR(1)
    	,@PKSelect VARCHAR(1000)
     
    BEGIN TRY
    	--You will need to change @TableName to match the table to be audited
    	 RAISERROR('Error', 16, 1)
    
    	SELECT @TableName = 't_TaskMaster'
    
    	-- date and user
    	SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
    
    	-- Action
    	IF EXISTS (
    			SELECT *
    			FROM INSERTED
    			)
    		IF EXISTS (
    				SELECT *
    				FROM DELETED
    				)
    			SELECT @Type = 'U'
    		ELSE
    			SELECT @Type = 'I'
    	ELSE
    		SELECT @Type = 'D'
    
    	-- get list of columns
    	SELECT *
    	INTO #ins
    	FROM inserted
    
    	SELECT *
    	INTO #del
    	FROM DELETED
    
    	IF @Type = 'I'
    		OR @Type = 'U'
    		SELECT @UserName = ModifiedBy
    		FROM #ins
    
    	IF @Type = 'D'
    		SELECT @UserName = updatedby
    		FROM #del
    
    	-- Get primary key columns for full outer join
    	SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
    		,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    	WHERE pk.TABLE_NAME = @TableName
    		AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    		AND c.TABLE_NAME = pk.TABLE_NAME
    		AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
    
    	-- Get primary key select for insert
    	SELECT @PKSelect = COALESCE(@PKSelect + '+', '') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+''>'''
    	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
    		,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    	WHERE pk.TABLE_NAME = @TableName
    		AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    		AND c.TABLE_NAME = pk.TABLE_NAME
    		AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
    
    	IF @PKCols IS NULL
    	BEGIN
    		RAISERROR (
    				'no PK on table %s'
    				,16
    				,- 1
    				,@TableName
    				)
    
    		RETURN
    	END
    
    	SELECT @field = 0
    		,@maxfield = MAX(ORDINAL_POSITION)
    	FROM INFORMATION_SCHEMA.COLUMNS
    	WHERE TABLE_NAME = @TableName
    
    	WHILE @field < @maxfield
    	BEGIN
    		BEGIN TRY
    			SELECT @field = MIN(ORDINAL_POSITION)
    			FROM INFORMATION_SCHEMA.COLUMNS
    			WHERE TABLE_NAME = @TableName
    				AND ORDINAL_POSITION > @field
    
    			SELECT @bit = (@field - 1) % 8 + 1
    
    			SELECT @bit = POWER(2, @bit - 1)
    
    			SELECT @char = ((@field - 1) / 8) + 1
    
    			IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
    				OR @Type IN (
    					'I'
    					,'D'
    					)
    			BEGIN
    				SELECT @fieldname = COLUMN_NAME
    				FROM INFORMATION_SCHEMA.COLUMNS
    				WHERE TABLE_NAME = @TableName
    					AND ORDINAL_POSITION = @field
    
    				SELECT @sql = 'insert t_Audit ( Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)select ''' + @Type + ''',''' + @TableName + ''',' + @PKSelect + ',''' + @fieldname + '''' + ',convert(varchar(1000),d.' + @fieldname + ')' + ',convert(varchar(1000),i.' + @fieldname + ')' + ',''' + @UpdateDate + '''' + ',''' + @UserName + '''' + ' from #ins i full outer join #del d' + @PKCols + ' where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
    
    				EXEC (@sql)
    			END 
    		END TRY
    
    		BEGIN CATCH		
    	  DECLARE @Content1 AS VARCHAR(1000) = error_message() + @PKCols + @fieldname + @PKSelect
    		EXEC sp_Email 'emailid@gmail.com'
    		,''
    		,'Error at insertion of Audit '
    		,@Content1
    		END CATCH
    	END
    END TRY
    
    BEGIN CATCH
    DECLARE @Content AS VARCHAR(1000) = error_message() + @PKCols + @fieldname + @PKSelect
    	EXEC sp_Email 'emailid@gmail.com'
    		,''
    		,'Error at insertion of Audit '
    		,@Content
    END CATCH
    


    For the testing trigger , I have added a line at starting.

     RAISERROR('Error', 16, 1)

    But When I update a table "update t_TaskMaster  SET Remarks='Test Remark' WHERE id='346882'" , it is giving an error.

    Msg 3930, Level 16, State 1, Procedure sp_send_dbmail, Line 64
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    Please help me to send a email when catch the error.

    Thank you.

    Wednesday, March 05, 2014 8:07 AM

Answers

  • This is the part from link which speaks about it

    Uncommittable Transactions and XACT_STATE

    If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. 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. This indicates that an uncommittable transaction was detected and rolled back.

    And this is an example

    Using TRY…CATCH with XACT_STATE

    The following example shows how to use the TRY…CATCH construct to handle errors that occur inside a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT is ON. This makes the transaction uncommittable when the constraint violation error occurs.

    USE AdventureWorks2012;
    GO
    
    -- Check to see whether this stored procedure exists.
    IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
        DROP PROCEDURE usp_GetErrorInfo;
    GO
    
    -- Create procedure to retrieve error information.
    CREATE PROCEDURE usp_GetErrorInfo
    AS
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_LINE () AS ErrorLine
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_MESSAGE() AS ErrorMessage;
    GO
    
    -- SET XACT_ABORT ON will cause the transaction to be uncommittable
    -- when the constraint violation occurs. 
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
            -- A FOREIGN KEY constraint exists on this table. This 
            -- statement will generate a constraint violation error.
            DELETE FROM Production.Product
                WHERE ProductID = 980;
    
        -- If the DELETE statement succeeds, commit the transaction.
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Execute error retrieval routine.
        EXECUTE usp_GetErrorInfo;
    
        -- Test XACT_STATE:
            -- If 1, the transaction is committable.
            -- If -1, the transaction is uncommittable and should 
            --     be rolled back.
            -- XACT_STATE = 0 means that there is no transaction and
            --     a commit or rollback operation would generate an error.
    
        -- Test whether the transaction is uncommittable.
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT
                N'The transaction is in an uncommittable state.' +
                'Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
    
        -- Test whether the transaction is committable.
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT
                N'The transaction is committable.' +
                'Committing transaction.'
            COMMIT TRANSACTION;   
        END;
    END CATCH;
    GO
    

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 05, 2014 8:21 AM
  • Start with throwing this trigger away. Instead write a program that generates the triggers if you have a lot of these. But you should not have dynamic SQL in the trigger body itself. The user may not have permissions to read metadata and your trigger would come back empty-handed. So the trigger must have static code. But as I said, the static code can be generated.

    As for the error message, any error that occurs in a trigger aborts execution and rollback transaction if there is no TRY-CATCH. If there is a CATCH handler, the transaction is still doomed. That is, the transaction is still active, but it cannot be committed, but it must be rolled back.

    After the ROLLBACK you can send the mail, although this is dubious practice. Better to write a log table about the error in that case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 8:36 AM
  • Can you please help me to add above logic in to my Trigger.

    I could not get how to use it.

    That's a false lead, so just ignore it. You need to roll back plain and simple.

    A trigger is an extension of the command that fired it, and if the trigger fails, the command has failed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 9:14 AM
  • Yes, that is inevitable. The statement failed. That should not pass unnoticed.

    But as I said: you should go back and rework the trigger to use static code. If want to generate code do that in a program, but do it in the trigger.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 11:23 AM

All replies

  • Hi,

    I had something similar. When you commit a transaction within a try catch you have to check the XACT_STATE first.

    Read here or google for try catch and transaction.

    http://technet.microsoft.com/en-us/library/ms175976.aspx
    (go down to Uncommittable Transactions and XACT_STATE)

    Alex


    • Edited by Alex Vary Wednesday, March 05, 2014 8:17 AM
    Wednesday, March 05, 2014 8:16 AM
  • Thank you for reply. Content not found for provided link.


    Wednesday, March 05, 2014 8:18 AM
  • This is the part from link which speaks about it

    Uncommittable Transactions and XACT_STATE

    If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. 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. This indicates that an uncommittable transaction was detected and rolled back.

    And this is an example

    Using TRY…CATCH with XACT_STATE

    The following example shows how to use the TRY…CATCH construct to handle errors that occur inside a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT is ON. This makes the transaction uncommittable when the constraint violation error occurs.

    USE AdventureWorks2012;
    GO
    
    -- Check to see whether this stored procedure exists.
    IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
        DROP PROCEDURE usp_GetErrorInfo;
    GO
    
    -- Create procedure to retrieve error information.
    CREATE PROCEDURE usp_GetErrorInfo
    AS
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_LINE () AS ErrorLine
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_MESSAGE() AS ErrorMessage;
    GO
    
    -- SET XACT_ABORT ON will cause the transaction to be uncommittable
    -- when the constraint violation occurs. 
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
            -- A FOREIGN KEY constraint exists on this table. This 
            -- statement will generate a constraint violation error.
            DELETE FROM Production.Product
                WHERE ProductID = 980;
    
        -- If the DELETE statement succeeds, commit the transaction.
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Execute error retrieval routine.
        EXECUTE usp_GetErrorInfo;
    
        -- Test XACT_STATE:
            -- If 1, the transaction is committable.
            -- If -1, the transaction is uncommittable and should 
            --     be rolled back.
            -- XACT_STATE = 0 means that there is no transaction and
            --     a commit or rollback operation would generate an error.
    
        -- Test whether the transaction is uncommittable.
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT
                N'The transaction is in an uncommittable state.' +
                'Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
    
        -- Test whether the transaction is committable.
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT
                N'The transaction is committable.' +
                'Committing transaction.'
            COMMIT TRANSACTION;   
        END;
    END CATCH;
    GO
    

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 05, 2014 8:21 AM
  • Can you please help me to add above logic in to my Trigger.

    I could not get how to use it.

    Thank you.

    Wednesday, March 05, 2014 8:35 AM
  • Start with throwing this trigger away. Instead write a program that generates the triggers if you have a lot of these. But you should not have dynamic SQL in the trigger body itself. The user may not have permissions to read metadata and your trigger would come back empty-handed. So the trigger must have static code. But as I said, the static code can be generated.

    As for the error message, any error that occurs in a trigger aborts execution and rollback transaction if there is no TRY-CATCH. If there is a CATCH handler, the transaction is still doomed. That is, the transaction is still active, but it cannot be committed, but it must be rolled back.

    After the ROLLBACK you can send the mail, although this is dubious practice. Better to write a log table about the error in that case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 8:36 AM
  • Can you please help me to add above logic in to my Trigger.

    I could not get how to use it.

    That's a false lead, so just ignore it. You need to roll back plain and simple.

    A trigger is an extension of the command that fired it, and if the trigger fails, the command has failed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 9:14 AM
  • I have added rollback like this ..

    BEGIN CATCH
    			ROLLBACK TRANSACTION;
    			DECLARE @Content1 AS VARCHAR(1000) = error_message() + @PKCols + @fieldname + @PKSelect		    
    			EXEC sp_Email 'emailId@gmail.com'
    			,''
    			,'Error at insertion of Audit '
    			,@Content1
    		END CATCH

    Now I able to get an email but it is showing another error .

    Msg 3609, Level 16, State 1, Line 1
    The transaction ended in the trigger. The batch has been aborted.

    Thank you.

    Wednesday, March 05, 2014 9:50 AM
  • Yes, that is inevitable. The statement failed. That should not pass unnoticed.

    But as I said: you should go back and rework the trigger to use static code. If want to generate code do that in a program, but do it in the trigger.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 11:23 AM