Some time ago I happened to comment on the post, "How do I track data changes in a database" ...
My comment didn't come out as understandable, so I wrote this little piece to explain how to audit changes in a table.
First I create a table to record the changed data:
CREATE TABLE [dbo].[Audits] (
[rowguid] uniqueidentifier NOT NULL,
[timestamp] datetime DEFAULT GETDATE() NOT NULL,
[table_name] VARCHAR(255) NOT NULL,
[user] VARCHAR(255) DEFAULT SUSER_SNAME() NOT NULL,
[data] xml NOT NULL,
CONSTRAINT [Audits_pk] PRIMARY KEY CLUSTERED ([rowguid], [timestamp])
Then I create a trigger for the tables I wish to audit, if you wish you can automate this for all your tables.
I use the contact table from the Adventureworks sample database to illustrate this example. It is important to add an UNIQUEIDENTIFIER column to your tables to make this work. The adventureworks database is full of such columns for replication use ...
CREATE TRIGGER [Person].[Contact_Audit_tr] ON [Person].[Contact]
WITH EXECUTE AS CALLER
FOR UPDATE, DELETE
SET NOCOUNT ON
DECLARE @deletedrows TABLE (id INT IDENTITY(1,1), rowguid UNIQUEIDENTIFIER)
DECLARE @rowcount INT
DECLARE @i INT
DECLARE @table_name VARCHAR(255)
DECLARE @data XML
DECLARE @rowguid UNIQUEIDENTIFIER
-- Obtain the name of the table the trigger belongs to
SELECT @table_name = OBJECT_NAME(parent_obj)
id = @@PROCID
-- Using a while loop instead of a cursor because cursors are evil :-)
-- I wish I could specify that a trigger was guaranteed to fire for a single row
-- How can I test a case where I get multiple rows in the deleted or insert table??
INSERT @deletedrows (rowguid)
SELECT rowguid FROM DELETED
SET @rowcount = @@ROWCOUNT
SET @i = 1
WHILE @i <= @rowcount
SELECT @rowguid = rowguid
id = @i
SET @data = (SELECT * FROM DELETED WHERE rowguid = @rowguid FOR XML AUTO, ELEMENTS)
INSERT INTO dbo.Audits (rowguid, table_name, data)
VALUES (@rowguid, @table_name, @data)
SET @i = @i+1
Thats it, you got the previous verisons of the changed rows stored as XML.