Service Broker-TestDatabase

USE master
IF DB_ID('TestDb1') IS NOT NULL
DROP DATABASE TestDb1
CREATE DATABASE TestDb1
GO
-- enable service broker
ALTER DATABASE TestDb1 SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE TestDb1 SET TRUSTWORTHY ON
GO
USE TestDb1
GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
DROP QUEUE InitiatorAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the initiator queue
CREATE QUEUE dbo.InitiatorAuditQueue
-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender]
AUTHORIZATION dbo
ON QUEUE dbo.InitiatorAuditQueue -- no contract means service can only be the initiator
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_SendAuditData
GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
@AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
SELECT @dlgIdExists = 1
-- Check if our database already has a dialog id that was previously used
-- Why reusing conversation dialogs is a good this is explaind here
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
-- very well
SELECT @dlgId = DialogId
FROM MasterAuditDatabase.dbo.AuditDialogs 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',
-- this is a MasterAuditDatabase Service Broker Id (change it to yours)
'4F5FF2B8-73D2-4EFE-84D4-36904579F205'
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 MasterAuditDatabase.dbo.AuditDialogs(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
GO
-- Create Sample Table
IF OBJECT_ID('Person') IS NOT NULL
DROP TABLE Person
GO
CREATE TABLE Person
(
ID INT PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
DateOfBirth SMALLDATETIME
)
-- Create Trigger that will audit data
GO
IF OBJECT_ID ('trgPersonAudit','TR') IS NOT NULL
DROP TRIGGER trgPersonAudit
GO
CREATE TRIGGER dbo.trgPersonAudit
ON Person
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @auditBody XML
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
SELECT @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
-- after update statement
IF EXISTS (SELECT * FROM deleted)
SELECT @DMLType = 'U'
-- after insert statement
ELSE
SELECT @DMLType = 'I'
END
-- get table name dynamicaly but
-- for performance this should be changed to constant in every trigger like:
-- SELECT @tableName = 'Person'
DECLARE @tableName sysname
SELECT @tableName = tbl.name
FROM sys.tables tbl
JOIN sys.triggers trg ON tbl.[object_id] = trg.parent_id
WHERE trg.[object_id] = @@PROCID
SELECT @auditBody =
'
' + DB_NAME() + '
' + @tableName + '
' + SUSER_SNAME() + '
' + @DMLType + '
' + CAST(@auditBody AS NVARCHAR(MAX)) + '
'
-- Audit data asynchrounously
EXEC dbo.usp_SendAuditData @auditBody
GO
-- we want this trigger to fire last for each command so that if there are other triggers
-- that update the table they finish their job before auditing
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'delete'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'insert'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'update'
-- Run code for TestDb1 in each database you wish to enable audting in.
-- Run some samples to see if it works:
USE TestDb1
-- will be audited
INSERT INTO Person
SELECT 1, 'name 1', 'surname 1', '19761215' UNION ALL
SELECT 2, 'name 2', 'surname 2', '19780705' UNION ALL
SELECT 3, 'name 3', 'surname 3', '19660305' UNION ALL
SELECT 4, 'name 4', 'surname 4', '19660609' UNION ALL
SELECT 5, 'name 5', 'surname 5', '19990606' UNION ALL
SELECT 6, 'name 6', 'surname 6', '20010202' UNION ALL
SELECT 7, 'name 7', 'surname 7', '19440404' UNION ALL
SELECT 8, 'name 8', 'surname 8', '19630523' UNION ALL
SELECT 9, 'name 9', 'surname 9', '19860929'
-- will be audited
UPDATE Person
SET DateOfBirth = '19800217'
WHERE id = 5
-- won't be audited because of the rollback
BEGIN TRANSACTION
UPDATE Person
SET DateOfBirth = '19701215'
WHERE id < 6
ROLLBACK
GO
-- check if auditing succeeded
USE MasterAuditDatabase
GO
SELECT * FROM MasterAuditTable
ORDER BY ChangeDate DESC
SELECT * FROM AuditDialogs