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(255) DEFAULT 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 UPDATE, DELETE
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 AUTO, ELEMENTS)
    INSERT INTO dbo.Audits (rowguid, table_name, data)
      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.