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
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)
IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SELECT @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
@DMLType = 'D'
END
ELSE
BEGIN
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
begin
SELECT @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
SELECT @DMLType = 'I'
end
END
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>'
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 DIALOG @dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'BAAEA6F1-C97E-4884-8651-2829A2049C46'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
IF @dlgIdExists = 0
BEGIN
INSERT INTO vwAuditDialogs(DbId, DialogId)
SELECT DB_ID(), @dlgId
END
;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