Store Insert, Update, Delete statements in a AUDIT table as any DML operations performed on a table

Question

Store Insert, Update, Delete statements in a AUDIT table as any DML operations performed on a table

bharat sethi

SSCommitted

Points: 1831
More actions
May 28, 2012 at 7:56 am

#261836

Dear All,
Hi! I am in a need where I have to store "Insert, Update, Delete" statements in an AUDIT table as any DML operations performed on a table. To understand this, please go through the below example: -
Suppose, we have a table named "TEST". It has four columns (ID, Name, DOB, CreditLimit). Create statement for this table is: -
Create Table Test213 (ID Int Identity(1,1), Name NVarchar(max), DOB DateTime, CreditLimit Numeric(16,2))
Now in this table, we are going to INSERT, UPDATE & DELETE records from front end (UI) and/or from back-end(SQL). The DML operations may be for SINGLE record or for multiple records.
Here, I need some mechanism by which I store the INSERT, UPDATE and/or DELETE statements for all the DML operations performed on this table either from UI or from SQL.
I need this because I have to synch. two tables with same structure but on different-different servers & they are not connected 24/7. Hence, when the connectivity is there I just get all the INSERT, UPDATE, DELETE statements from the AUDIT table & run them directly on another server.
Please guide.
Change Data Capture
Enable and Disable Change Data Capture

Cheers,

Hope this helps,
Rock from VbCity

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You really should consider SQL Server Replication for this. It is tailor-made for just this situation.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

I've done it before in a place when standard SQL replication wasn't allowed for stupid reason.

You will need to create custom triggers to capture change and build appropriate DML. The following example shows what the trigger can look like. It inserts the DML statements into sync.Queue table:

------------------------------------------------------------------------------

-- Auto-generated Sync Trigger

------------------------------------------------------------------------------

CREATE TRIGGER [dbo].[t_Sync_MyTable] ON [dbo].[MyTable] AFTER INSERT,DELETE,UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT 1 FROM inserted)

IF EXISTS(SELECT 1 FROM deleted)

INSERT INTO sync.Queue SELECT getutcdate() utcdate,'[dbo].[MyTable]','<Keys><Key Name="MyTableIdColumn" DataType="int" Length="0" AllowsNull="0">'+CAST(MyTableIdColumn as varchar(255))+'</Key></Keys>',N'UPDATE [dbo].[MyTable] SET [Column1]='+Isnull(cast(Column1 as nvarchar),'null')+',[Column2]='+Isnull('''' + Replace(Venue,'''','''''') +'''' ,'null') + ' WHERE [MyTableIdColumn]='+Isnull(cast(MyTableIdColumn as nvarchar),'null') FROM Inserted

ELSE

INSERT INTO sync.Queue SELECT getutcdate() utcdate,'[dbo].[MyTable]','<Keys><Key Name="MyTableIdColumn" DataType="int" Length="0" AllowsNull="0">'+CAST(MyTableIdColumn as varchar(255))+'</Key></Keys>',N'INSERT INTO [dbo].[MyTable]([MyTableIdColumn],[Column1],[Column2] VALUES ('+Isnull(cast(MyTableIdColumn as nvarchar),'null')+','+Isnull(cast(Column1 as nvarchar),'null')+','+Isnull('''' + Replace(MyTableIdColumn,'''','''''') +'''' ,'null') FROM Inserted

ELSE

INSERT INTO sync.Queue SELECT getutcdate() utcdate,'[dbo].[MyTable]','<Keys><Key Name="MyTableIdColumn" DataType="int" Length="0" AllowsNull="0">'+CAST(MyTableIdColumn as varchar(255))+'</Key></Keys>',N'DELETE FROM [dbo].[MyTable] WHERE [MyTableIdColumn]='+Isnull(cast(MyTableIdColumn as nvarchar),'null') FROM deleted

END

The real triggers can capture some more details and also can deal with CONTEXT_INFO if needed...

And of cause, these triggers are auto-generated...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 3

I agree with RBarryYoung. However, if there is some reason that you cannot use replication, I would recommend you research the following:

Paul Nielsen's open source Auto Audit: http://autoaudit.codeplex.com

Blog post about it: http://sqlblog.com/blogs/paul_nielsen/archive/2007/01/15/codegen-to-create-fixed-audit-trail-triggers.aspx

Mladen Prajdic's amazing article "Centralized Asynchronous Auditing with Service Broker": http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 4

Hi bharat sethi

How are you doing with this question? I read today about Change Data Capture a feature introduced since SQL 2008; it seems to do exactly what you want to achieve, quoting one of the articles at MSDN

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

These are a couple of links describing this technology: