SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table.

SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table

—
— FILENAME: CDCchange_tablesAudit.SQL
—
— AUTHOR:  Chris Skorlinski
—
— DESCRIPTION: SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table.
— This script is used to audit the activity in the changes_tables table created when
—  you enable Change Data Capture for a database.  The script creates an AUDIT table then
—  adds INS/UPD/DEL triggers to the CDC changes_tables table.  These triggers should only be used
—  for a short period while troubleshooting CDC.
— 
—

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

/****** Object:  Table [cdc].[change_tables_AUDIT]    Script Date: 08/31/2010 19:11:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cdc].[change_tables_AUDIT]’) AND type in (N’U’))
DROP TABLE [cdc].[change_tables_AUDIT]
GO

CREATE TABLE [cdc].[change_tables_AUDIT](
[object_id] [int] NOT NULL,
[version] [int] NULL,
[source_object_id] [int] NULL,
[capture_instance] [sysname] NOT NULL,
[start_lsn] [binary](10) NULL,
[end_lsn] [binary](10) NULL,
[supports_net_changes] [bit] NULL,
[has_drop_pending] [bit] NULL,
[role_name] [sysname] NULL,
[index_name] [sysname] NULL,
[filegroup_name] [sysname] NULL,
[create_date] [datetime] NULL,
[partition_switch] [bit] NOT NULL,
[SPID]    [smallint] NULL,
  [ApplicationName] [nvarchar](128) NULL,
  [twhen]    [datetime] NULL,
[hostname]   [nvarchar]( 255 ) NULL,
[inputbuffer]       [nvarchar]( 4000 ) NULL,
  [type]    [varchar]( 2 ) NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ndxchange_tables_AUDIT] ON [cdc].[change_tables_AUDIT] (twhen ASC)
GO

if object_id (‘[cdc].[trgchange_tablesINS]’) is not null
begin
  drop trigger [cdc].[trgchange_tablesINS]
end
GO

/*
** Create the INSERT trigger for change_tables
*/

CREATE TRIGGER [cdc].[trgchange_tablesINS]
ON [cdc].[change_tables]
FOR INSERT
AS

if 0 = (select count(*) from inserted) return
— Create table variable to receive output of dbcc inputbuffer
—
DECLARE @InputBuffer TABLE ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))
INSERT INTO @InputBuffer exec(‘DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS’)
INSERT INTO [cdc].[change_tables_AUDIT]
SELECT *, @@SPID, APP_NAME(), GETDATE(), HOST_NAME(), (select eventinfo from @InputBuffer) as command, ‘I’ FROM inserted
GO

/*
** Create the DELETE trigger for change_tables
*/
if object_id (‘[cdc].[trgchange_tablesDEL]’) is not null
begin
drop trigger [cdc].[trgchange_tablesDEL]
end
GO

CREATE TRIGGER [cdc].[trgchange_tablesDEL]
ON [cdc].[change_tables]
FOR DELETE
AS
if 0 = (select count(*) from deleted) return

— Create table variable to receive output of dbcc inputbuffer
—
DECLARE @InputBuffer TABLE ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))

INSERT INTO @InputBuffer exec(‘DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS’)

INSERT INTO [cdc].[change_tables_AUDIT]
SELECT *, @@SPID, APP_NAME(), GETDATE(), HOST_NAME(), (select eventinfo from @InputBuffer) as command, ‘D’ FROM deleted
GO

/*
** Create the UPDATE trigger for change_tables
*/

if object_id (‘[cdc].[trgchange_tablesUPD]’) is not null
begin
     drop trigger [cdc].[trgchange_tablesUPD]
end
GO
CREATE TRIGGER [cdc].[trgchange_tablesUPD]
ON [cdc].[change_tables]
FOR UPDATE
AS

if 0 = (select count(*) from inserted) return

DECLARE @InputBuffer TABLE ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))

INSERT INTO @InputBuffer exec(‘DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS’)
INSERT INTO [cdc].[change_tables_AUDIT]
SELECT *, @@SPID, APP_NAME(), GETDATE(), HOST_NAME(), (select eventinfo from @InputBuffer) as command, ‘UD’ FROM deleted
INSERT INTO [cdc].[change_tables_AUDIT]
SELECT *, @@SPID, APP_NAME(), GETDATE(), HOST_NAME(), (select eventinfo from @InputBuffer) as command, ‘UI’ FROM inserted
GO