Updating a table (using an audit trigger using Service Broker) in a transaction

We introduced audit capabilities using a service broker and implemented triggers in the tables that need to be audited. The problem we are facing is when we try to update the table being checked from a transaction, it throws an error -

The current transaction cannot and cannot support operations that are written to the log file. Cancel the transaction.

However, if we remove the trigger from the checked table, everything works absolutely fine. Is it possible to update the table (with the trigger) inside the transaction, or are we missing something at our end?

Update Transaction

BEGIN TRAN
    update  ActivationKey set OrderLineTransactionId = @orderLineTransactionId, LastUpdated = getUtcdate(), [Status] =2  
    where   PurchaseTransactionId = @transactionid 
        -- Rollback the transaction if there were any errors
            IF @@ERROR <> 0 
                ROLLBACK
            ELSE        
                COMMIT TRAN
END TRAN                

Trigger

ALTER TRIGGER [dbo].[ActivationKey_AuditTrigger]
     ON  [dbo].[ActivationKey]
    AFTER INSERT, UPDATE, DELETE 
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @auditBody XML
        Declare @newData nvarchar(MAX)
        DECLARE @DMLType CHAR(1)    
        -- after delete statement
        IF NOT EXISTS (SELECT * FROM inserted)
        BEGIN   
            SELECT  @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
                    @DMLType = 'D'
        END 
        -- after update or insert statement
        ELSE
        BEGIN
                --after Update Statement
            IF EXISTS (SELECT * FROM deleted)
              begin
                    SELECT      @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS)
                    SELECT      @newData = (select * FROM Inserted AS t FOR XML AUTO, ELEMENTS)
                    SELECT      @DMLType = 'U'
              end
              ELSE -- after insert statement
              begin
                    SELECT      @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
                    SELECT      @DMLType = 'I'
              end
        END

        -- get table name dynamicaly but
        DECLARE @tableName sysname 
        SELECT  @tableName = 'ActivationKey'

        SELECT @auditBody = 
            '<AuditMsg>
                <SourceDb>' + DB_NAME() + '</SourceDb>
                <SourceTable>' + @tableName + '</SourceTable>
                <UserId>' + SUSER_SNAME() + '</UserId>
                <DMLType>' + @DMLType + '</DMLType>
                <ChangedData>' + CAST(@auditBody AS NVARCHAR(MAX)) + '</ChangedData>
                <NewData>' + isnull(@newData,'') + '</NewData>
            </AuditMsg>'
        -- Audit data asynchrounously
        EXEC dbo.procAuditSendData @auditBody
    END 

Stored Proc (procAuditSendData) called from a trigger

ALTER PROCEDURE [dbo].[procAuditSendData]

(   @AuditedData XML )          DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT       SELECT @dlgIdExists = 1

    SELECT  @dlgId = DialogId
    FROM    vwAuditDialogs AD 
    WHERE   AD.DbId = DB_ID()
    IF  @dlgId IS NULL
    BEGIN 
        SELECT @dlgIdExists = 0
    END

    -- Begin the dialog, either with existing or new Id
    BEGIN DIALOG @dlgId
        FROM SERVICE    [//Audit/DataSender]                                               
        TO SERVICE      '//Audit/DataWriter', 
                'BAAEA6F1-C97E-4884-8651-2829A2049C46'
        ON CONTRACT     [//Audit/Contract]
    WITH ENCRYPTION = OFF;

    -- add our db dialog to AuditDialogs table if it doesn't exist yet
    IF @dlgIdExists = 0
    BEGIN 
        INSERT INTO vwAuditDialogs(DbId, DialogId)
        SELECT  DB_ID(), @dlgId
    END
    --SELECT @AuditedData

    -- Send our data to be audited
    ;SEND ON CONVERSATION @dlgId    
    MESSAGE TYPE [//Audit/Message] (@AuditedData)
END TRY
BEGIN CATCH
    INSERT INTO AuditErrors (
            ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage, 
            ErrorSeverity, ErrorState, AuditedData)
    SELECT  ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), 
            ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
END CATCH

+3
2

ERROR_PROCEDURE() .. , ROLLBACK TRANSACTION, , CATCH. TRY... CATCH Transact SQL, " ". , (uspLogError), :

BEGIN CATCH
    -- Call procedure to print error information.
    EXECUTE dbo.uspPrintError;

    -- Roll back any active or uncommittable transactions before
    -- inserting information in the ErrorLog.
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH; 

( ​​ ), , , XML. , .

+1

, :

- , . . procAuditSendData update/insert/delete ( ). procAuditSendData . , datareader datawriter. , .

+1

All Articles