I don't think it's a good idea to audit ALL tables in the database.

For the most important tables (which usually are small so the overhead is minimal), I use the following solution (SQL 2005):

The logging table (only one for the entire database):

create table DMLLogs(

DMLLogID int identity primary key,

TableName nvarchar (128),

DateCreated datetime DEFAULT (getdate()),

OldValues xml NULL,

NewValues xml NULL)

The trigger for the table which you want to audit; The only thing that needs to be changed for another table is the table name. You can use the solution presented early in this post:

Select @TableName = Object_name(Parent_obj)

from dbo.SysObjects

where id = @@PROCID;

in this case the trigger will be exactly the same for all audited tables

create trigger MyTable_T_Log on MyTable

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 LogDMLEvent

@TableName = 'MyTable',

@Deleted = @D,

@Inserted = @I

The procedure LogDMLEvent is:

create procedure LogDMLEvent

@TableName sysname,

@Deleted xml,

@Inserted xml

as

if @Deleted is not null or @Inserted is not null

insert into DMLLogs (TableName, OldValues, NewValues)

values (@TableName, @Deleted, @Inserted)

you can include in this procedure (and in the DMLLogs table) also the user name, connection info ....

So in the logging table you will have xml representations of inserted and deleted tables. You can very easy put them into a view to mimic the original table structure (one view for each table).