Updating a table (with audit trigger using Service Broker) in a Transaction

We have implemented Auditing capability using service broker and have implemented triggers on the tables that need to be audited. The issue we are facing is when we try to update an auditable table from within a transaction, it throws up an error -

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

However, if we remove the trigger from the auditable table, it all works absolutely fine. is it not possible to have a table (with trigger) be updated within a 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 within the trigge开发者_开发知识库r

ALTER PROCEDURE [dbo].[procAuditSendData]

( @AuditedData XML ) AS BEGIN BEGIN TRY 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's 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

END

You can still access the ERROR_PROCEDURE() etc functions after you've issued a ROLLBACK TRANSACTION, which is what you need to do here, in your CATCH block. Look at the examples in Using TRY...CATCH in Transact SQL, especially look at the code in "error-handling example". The procedure it calls to log the errors (uspLogError) appears a couple of samples above it:

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; 

As to what the underlying error is (that is currently erroring in your error reporting), if I had to guess it would be that the contract for your messages can't cope with multiple rows of data appearing in the XML. But we'd need to see the contract to confirm that.

I had the same error, since I used same example you did: service broker audit

I finally managed to get error of this message and it was the matter of security. You have separate database for audit records. Your procAuditSendData is executed in the context of your update/insert/delete command (it uses same credentials). In my case, user from procAuditSendData context had no rights to access audit database. In order to fix your error you have to add that context user on your separate audit database and grant him the rights of datareader and datawriter. I did this and after that everything worked like a charm.