SQLServerWiki

“The Only Thing That Is Constant Is Change”

How to Audit Deletes to the Conflict Table.

Posted by database-wiki on April 11, 2016

We wanted to see when deletes occur to a Merge Replication Conflict Table.

The following code creates a table then creates a trigger on the Conflict table to
log deletes. This can also be used to audit Inserts or Updates.

— Conflict Audit table will have one record for each record deleted in a Conflict Table.
— Run this command once to build the Conflict Audit Table

CREATE TABLE [dbo].[AuditConflictDelete] (
[CustomerID] [nchar] (5) NOT NULL ,
[CompanyName] [nvarchar] (40) NOT NULL ,
[ContactName] [nvarchar] (30) NULL ,
[ContactTitle] [nvarchar] (30) NULL ,
[Address] [nvarchar] (60) NULL ,
[City] [nvarchar] (15) NULL ,
[Region] [nvarchar] (15) NULL ,
[PostalCode] [nvarchar] (10) NULL ,
[Country] [nvarchar] (15) NULL ,
[Phone] [nvarchar] (24) NULL ,
[Fax] [nvarchar] (24) NULL ,
[New] [char] (10) NULL ,
[rowguid] [uniqueidentifier] NOT NULL ,
[origin_datasource] [nvarchar] (255) NULL ,
[conflict_type] [int] NULL ,
[reason_code] [int] NULL ,
[reason_text] [nvarchar] (720) NULL ,
[pubid] [uniqueidentifier] NULL ,
[MSrepl_create_time] [datetime] NULL,
[SPID] INTEGER NULL,
[ApplicationName] VARCHAR( 128 ) NULL,
[twhen] DATETIME NULL,
[hostname] VARCHAR( 128 ) NULL,
[inputbuffer] NVARCHAR( 255 ) NULL,
[type] VARCHAR( 2 ) NULL
)
GO

CREATE CLUSTERED INDEX AuditConflict on AuditConflictDelete(twhen)
GO

— A Delete trigger is added to each Conflict table
— Run once for each Conflict table, change the Trigger Name trg_<conflict
table>_DEL
— and the “On <conflict table>” with your conflict table names.

CREATE TRIGGER trg_<conflict table>_DEL
ON [dbo].[<conflict table>]
FOR DELETE
AS
DECLARE @command NVARCHAR(255)

CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo]
NVARCHAR(255))
INSERT INTO #InputBuffer exec(‘DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS’)
SELECT @command=eventinfo from #InputBuffer

INSERT INTO [dbo].[AuditConflictDelete]
SELECT *, @@SPID, APP_NAME(), GETDATE(), HOST_NAME(), @command, ‘D’ FROM deleted
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: