SQL Server 2005 How can I set up an audit table that records the column name updated?

Question

given this table definition

create table herb.app (appId int identity primary key
 , application varchar(15) unique
 , customerName varchar(35),LoanProtectionInsurance bit 
 , State varchar(3),Address varchar(50),LoanAmt money
 ,addedBy varchar(7) not null,AddedDt smalldatetime default getdate())

I believe changes will be minimal, usually only a single field, and very sparse.

So I created this table:

create table herb.appAudit(appAuditId int primary key
 , field varchar(20), oldValue varchar(50),ChangedBy varchar(7) not null,AddedDt smalldatetime default getdate())

How in a trigger can I get the column name of the value of what was changed to store it? I know how to get the value by joining the deleted table.

Answer 1

something like this for each field you want to track

    if UPDATE(Track_ID)
begin

insert into [log].DataChanges
(
 dcColumnName,
 dcID,
 dcDataBefore,
 dcDataAfter,
 dcDateChanged,
 dcUser,
 dcTableName
)
select
'Track_ID',
d.Data_ID,
coalesce(d.Track_ID,-666),
coalesce(i.Track_ID,-666),
getdate(),
@user,
@table
from inserted i
 join deleted d on i.Data_ID=d.Data_ID
  and coalesce(d.Track_ID,-666)<>coalesce(i.Track_ID,-666)

end

'Track_ID' is the name of the field, and d.Data_ID is the primary key of the table your tracking. @user is the user making the changes, and @table would be the table your keeping track of changes in case you're tracking more than one table in the same log table

Answer 2

If you really need this kind of auditing in a way that's critical to your business look at SQL Server 2008's Change Data Capture feature. That feature alone could justify the cost of an upgrade.