Auditing SQL Server data changes
The CDC should is just a means to an end in my opinion. I have implemented audit trail solutions in the past and they have involved the use of Triggers. This got to be very messy and performance intensive for highly transactional databases.
What the CDC gives you is the ability to log the audit data without the use of triggers, but you still need a means to take that data into a permanent table. This can be done with a mirror table for each table to be audited or a single table that tracks all the changes to all the tables (I have done the latter).
Here are some links with additional information on how it was done using triggers:
SQL Audit Trail
sql-server-history-table-populate-through-sp-or-trigger
Here's an open source audit tracking solution that uses LINQ: DoddleAudit
Log record changes in SQL server in an audit table
Take a look at this article on Simple-talk.com by Pop Rivett. It walks you through creating a generic trigger that will log the OLDVALUE and the NEWVALUE for all updated columns. The code is very generic and you can apply it to any table you want to audit, also for any CRUD operation i.e. INSERT, UPDATE and DELETE. The only requirement is that your table to be audited should have a PRIMARY KEY (which most well designed tables should have anyway).
Here's the code relevant for your GUESTS Table.
- Create AUDIT Table.
IF NOT EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO
- CREATE an UPDATE Trigger on the GUESTS Table as follows.
CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
SELECT @TableName = 'GUESTS'
-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END
GO
Find changes from an audit table
This will provide the result as specified in your question, though it is far from a sensible or scalable solution. If at all possible, I would recommend you completely revisit your change auditing:
declare @EmpAudit table (
empID int
, empName varchar(50)
, empAge int
, auditDataState varchar(50)
, auditDMLAction varchar(50)
, auditUser varchar(50)
, auditDateTime datetime
, updateColumns varchar(50)
);
insert into @EmpAudit values
(1, 'Alex', 22, 'New', 'Insert','c@a.com',getdate(),''),
(2, 'Jhonny', 18, 'New', 'Insert','c@a.com',getdate()-0.5,''),
(2, 'Jhonny', 18, 'Old', 'Update','b@a.com',getdate()-1,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'New', 'Update','b@a.com',getdate()-1.5,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'Old', 'Update','a@a.com',getdate()-2,'Employee Age'),
(2, 'Jone', 30, 'New', 'Update','a@a.com',getdate()-2.5,'Employee Age'),
(2, 'Jone', 30, 'Old', 'Update','a@a.com',getdate()-3,'Employee Age'),
(2, 'Jone', 20, 'New', 'Update','a@a.com',getdate()-3.5,'Employee Age'),
(2, 'Jone', 20, 'Old', 'Update','a@a.com',getdate()-4,'Employee Name'),
(2, 'Jhone', 20, 'New', 'Update','a@a.com',getdate()-4.5,'Employee Name');
with d as
(
select empID
,empName
,empAge
,auditDataState
,auditDMLAction
,auditUser
,auditDateTime
,updateColumns
,row_number() over (partition by empID order by auditDateTime) as rn
from @EmpAudit
)
select case when o.empName <> n.empName then 'Employee Name : from ' + o.empName + ' to ' + n.empName else '' end
+case when charindex(',',o.UpdateColumns) > 0 then ', ' else '' end
+case when o.empAge <> n.empAge then 'Employee Age : from ' + cast(o.empAge as varchar(3)) + ' to ' + cast(n.empAge as varchar(3)) else '' end as Change
from d as o
join d as n
on o.empID = n.empID
and o.updateColumns = n.updateColumns
and o.rn = n.rn+1
and n.auditDataState = 'New'
where o.auditDataState = 'Old';
Output:
Change
-----------------------------------------------------------------
Employee Name : from Jone to Jhone
Employee Age : from 30 to 20
Employee Age : from 25 to 30
Employee Name : from Jhonny to Jone, Employee Age : from 18 to 25
Auditing data changes in SQL Server 2008
A correctly written trigger should be fast enough.
You could also look at Change Data Capture
- Auditing in SQL Server 2008
I quite often use AutoAudit:
AutoAudit is a SQL Server (2005, 2008, 2012) Code-Gen utility that creates
Audit Trail Triggers with:Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing
INT) columns to tableInsert event logged to Audit table
Updates old and new values logged to Audit table Delete logs all
final values to the Audit tableview to reconstruct deleted rows
UDF to reconstruct Row History
Schema Audit Trigger to track schema changes
Re-code-gens triggers when Alter Table changes the table
Update: (Original edit was rejected, but I'm re-adding it):
A major upgrade to version 3.20 was released in November 2013 with these added features:
Handles tables with up to 5 PK columns
Performance improvements up to 90% faster than version 2.00
Improved historical data retrieval UDF
Handles column/table names that need quotename [ ]
Archival process to keep the live Audit tables smaller/faster but retain the older data in archive AutoAudit tables
Best way to implement an audit trail in SQL Server?
There are many ways to do that; it depends which version of SQL Server you are using.
Here are few
Audit trail with shadow table and trigger Here is the link
Also you can consider to use SQL Server 2008 Audit feature Here is the link
Related Topics
How to Include Excluded Rows in Returning from Insert ... on Conflict
SQL Server: Replace Invalid Xml Characters from a Varchar(Max) Field
Exporting a Clob to a Text File Using Oracle SQL Developer
SQL Pulling a Row for Next or Previous Row of a Current Row
Using Django How to Combine Two Queries from Separate Models into One Query
MySQL - Difference Between in and Exist
Create a Delimitted String from a Query in Db2
How to Add a Subtotal Row in SQL
SQL Server: Calculating Date Ranges
What Is "Connect Timeout" in SQL Server Connection String
How to Set a Default Row for a Query That Returns No Rows
How to Compare Two Columns for Equality in SQL Server