Just to provide a complete solution for everyone for George's suggestion see below. We've used this successfully and performance is excellent. For tables with binary/text columns you will need to change trigger a bit.

Hope this helps. This will only work with Sql Server 2005 and above.

CREATE TABLE [dbo].[Audit](

[AuditId] [bigint] IDENTITY(1,1) NOT NULL,

[TableName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DateCreated] [datetime] NULL DEFAULT (getdate()),

[UserId] [int] NULL,

[SystemUser] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Operation] AS (case when [OldValue] IS NULL then 'Insert' else case when [NewValue] IS NULL then 'Delete' else 'Update' end end),

[OldValue] [xml] NULL,

[NewValue] [xml] NULL,

[OldBinaryValue] [varbinary](max) NULL,

[NewBinaryValue] [varbinary](max) NULL

)

CREATE TABLE [dbo].[AuditTable](

[TableName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IdentityColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[UserIdColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED

(

[TableName] ASC

)

create proc [dbo].[SaveAudit]

@OldValues xml,

@NewValues xml

as

Declare @TableName varchar(128), @SystemUser varchar(80), @userid int,

@ColumnName varchar(128), @Operation varchar(10), @sql varchar(1000)

SELECT @SystemUser=system_user

SELECT @TableName=object_name(resource_associated_entity_id)

FROM sys.dm_tran_locks

WHERE request_session_id = @@spid and resource_type = 'OBJECT'

Insert Into Audit (TableName, UserId, SystemUser, OldValue, NewValue)

Values (@TableName, null, @SystemUser, @OldValues, @NewValues)

go

CREATE proc [dbo].[GetAuditInfo]

@TableName varchar(128),

@StartDate datetime,

@EndDate datetime

as

set nocount on

Declare @x int, @max-2 int, @sql varchar(4000), @ColumnName varchar(200)

set @x=1

set @sql=''

SELECT @max-2=count(c.[name])

FROM sys.all_columns c

INNER JOIN sys.all_objects o ON c.object_id = o.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE o.[name] = @TableName AND o.[type] = 'U'

while (@x <= @max-2)

Begin

SELECT @ColumnName=c.[name]

FROM sys.all_columns c

INNER JOIN sys.all_objects o ON c.object_id = o.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE o.[name] = @TableName and c.column_id=@x AND o.[type] = 'U'

set @sql=@sql+'v.value(''@'+@ColumnName+''', ''varchar(8000)'') as '+@ColumnName

if @x<@max

set @sql=@sql+', '

set @x=@x+1

End

Declare @sql2 varchar(8000)

set @sql2='SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+

' Into ##340834audit FROM Audit CROSS APPLY OldValue.nodes(''/deleted'') x(v)

Where TableName='''+@TableName+''''

exec(@sql2)

set @sql2=''

set @sql2='Insert Into ##340834audit SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+

' FROM Audit CROSS APPLY NewValue.nodes(''/inserted'') x(v)

Where TableName='''+@TableName+''''

exec(@sql2)

select * from ##340834audit Order By TableName, DateCreated, SystemUser, Operation

drop table ##340834audit

go

And finally.....a generic audit trigger for your tables

Create trigger [dbo].[AuditYourTable] on [dbo].[YourTable]

for insert, update, delete

as

declare @i xml, @d xml

set @i = (select * from inserted for xml auto)

set @d = (select * from deleted for xml auto)

exec SaveAudit @d, @i

GO