use master;

if db_id('DBAuditExample') is not null
begin
 alter database DBAuditExample set single_user with rollback immediate;
 drop database DBAuditExample;
end;

create database DBAuditExample;
alter database DBAuditExample set recovery simple;
alter database DBAuditExample set enable_broker with rollback immediate;
go

use DBAuditExample;
go

if exists(select * from sys.server_event_notifications where name = 'enSimpleAudit')
 drop event notification enSimpleAudit on server;
go

if object_id('dbo.spHandleMessages', 'P') is null
 exec('create procedure dbo.spHandleMessages as begin set nocount on; end;');
go

create queue dbo.qSimpleAudit with
 status = on,
 retention = off,
 activation (status = on, procedure_name = dbo.spHandleMessages, max_queue_readers = 5, execute as owner),
 poison_message_handling (status = on);
go

create service svcSimpleAudit
 authorization dbo
 on queue dbo.qSimpleAudit
 (
  [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
 );
go

create table dbo.SimpleAuditData
(
 ad_id int identity,
 ad_OccurredAt datetime not null,
 ad_Login sysname,
 ad_Application nvarchar(128),
 ad_Host nvarchar(128),
 ad_ShortText nvarchar(128),
 ad_LongText nvarchar(4000),
 constraint PK_SimpleAuditData primary key (ad_id)
);
go

alter procedure dbo.spHandleMessages
as
begin
 set nocount on;

 declare @handle uniqueidentifier, @message xml, @message_type sysname;

 waitfor
 (
  receive top (1)
   @handle = conversation_handle,
   @message_type = message_type_name,
   @message = message_body
  from
   dbo.qSimpleAudit
 ),
 timeout 1000;

 if @@rowcount = 0
  return 0;

 if @message_type = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
  with s as
  (
   select
    n.value('EventType[1]', 'sysname') as EventType,
    n.value('PostTime[1]', 'datetime') as PostTime,
    n.value('TextData[1]', 'sysname') as TextData,
    n.value('BinaryData[1]', 'varbinary(8000)') as BinaryData,
    n.value('DatabaseID[1]', 'int') as DatabaseID,
    n.value('DatabaseName[1]', 'sysname') as DatabaseName,
    n.value('ApplicationName[1]', 'sysname') as ApplicationName,
    n.value('HostName[1]', 'sysname') as HostName,
    n.value('SessionLoginName[1]', 'sysname') as SessionLoginName
   from
    @message.nodes('/EVENT_INSTANCE') x(n)
  )
  insert into dbo.SimpleAuditData
   (ad_OccurredAt, ad_Login, ad_Application, ad_Host, ad_ShortText, ad_LongText)
   select
    s.PostTime, s.SessionLoginName, s.ApplicationName, s.HostName, s.TextData, cast(s.BinaryData as nvarchar(4000))
   from
    s
   where
    DatabaseID = db_id();

 if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
  end conversation @handle with cleanup;

 if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
  end conversation @handle with cleanup;

end;
go

create event notification enSimpleAudit
on server
for USERCONFIGURABLE_0
to service 'svcSimpleAudit', 'current database';
go