2006-09-04

Audit table data changes in SQL Server 2005

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(255DEFAULT SUSER_SNAME() NOT NULL,
  
[data] xml NOT NULL,
  
CONSTRAINT [Audits_pk] PRIMARY KEY CLUSTERED ([rowguid][timestamp])
)
ON [PRIMARY]




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 UPDATEDELETE
AS
BEGIN
  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)
  
FROM
    
sys.sysobjects
  
WHERE
    
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
  
BEGIN
    SELECT 
@rowguid rowguid
    
FROM @deletedrows
    
WHERE
      
id @i
    
SET @data (SELECT FROM DELETED WHERE rowguid @rowguid FOR XML AUTOELEMENTS)
    
INSERT INTO dbo.Audits (rowguidtable_namedata)
      
VALUES (@rowguid@table_name@data)
    
SET @i @i+1
  
END
END



Thats it, you got the previous verisons of the changed rows stored as XML.

2 comments:

Anonymous said...

OK, so how do you mine the data changes with T-SQL now?

Brett

http://weblogs.sqlteam.com/brettk/

cramer said...

Newbie question, what does the following statement do?

INSERT @deletedrows (rowguid)