CODEWITHSUNDEEP
sql-servertriggersaudit
Maslow
Maslow

Reputation: 18746

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

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.

Upvotes: 1

Views: 2370

Answers (4)

Hafthor
Hafthor

Reputation: 16916

Here's my quick and dirty audit table solution. (from http://freachable.net/2010/09/29/QuickAndDirtySQLAuditTable.aspx)

CREATE TABLE audit(
  [on] datetime not null default getutcdate(),
  [by] varchar(255) not null default system_user+','+AppName(),
  was xml null,
  [is] xml null
)

CREATE TRIGGER mytable_audit ON mytable for insert, update, delete as
INSERT audit(was,[is]) values(
  (select * from deleted as [mytable] for xml auto,type),
  (select * from inserted as [mytable] for xml auto,type)
)

Upvotes: 0

Malcolm
Malcolm

Reputation: 410

Use the inserted and deleted tables. Nigel Rivett wrote a great generic audit trail trigger using these tables. It is fairly complex SQL code, but it highlights some pretty cool ways of pulling together the information and once you understand them you can create a custom solution using his ideas as inspiration, or you could just use his script.

Here are the important ideas about the tables:

  • On an insert, inserted holds the inserted values and deleted is empty.
  • On an update, inserted holds the new values and deleted holds the old values.
  • On a delete, deleted holds the deleted values and inserted is empty.
  • The structure of the inserted and deleted tables (if not empty) are identical to the target table.
  • You can determine the column names from system tables and iterate on them as illustrated in Nigel's code.

    if exists (select * from inserted)
        if exists (select * from deleted)
            -- this is an update
            ...
        else
            -- this is an insert
            ...
    else
        -- this is a delete
        ...

-- For updates to a specific field
SELECT d.[MyField] AS OldValue, i.[MyField] AS NewValue, system_user AS User
FROM inserted i
INNER JOIN deleted d ON i.[MyPrimaryKeyField] = d.[MyPrimaryKeyField]

-- For your table
SELECT d.CustomerName AS OldValue, i.CustomerName AS NewValue, system_user AS User
FROM inserted i
INNER JOIN deleted d ON i.appId = d.appId

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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.

Upvotes: 1

DForck42
DForck42

Reputation: 20387

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

Upvotes: 0

Related Questions