sql server programming: Hi,
i am trying to create a generic trigger which will audit
changes to data in a table.
Now it is possible to know which column has been changed by using
Update() or Column_update()

But even with dynamic SQL i can't figure out how to insert the values
i want into an audit table.

Inserting DateTime of Change and the user name is easy. But only
during run time after I have checked will i know which column has had
it's valued changed. And there could be multiple columns whcih have
changed.

I want to insert information about each changed column into an audit
table (TableName, FieldName, Oldvalue, Newvalue,Timeofchange,
Changedby).

All tables would have a copy of this trigger but all would write to a
single Audit table in the database.

How do you write the insert statement for the audit table so it picks
up those columns in the inserted and deleted tables which have
changed. e.g. 3 columns updated in one row should create 3 records in
the audit table

regards

Erick

I would be very wary of trying to audit all changes to all tables to a
single audit table. Consider the impact on performance before implementing
such a scheme.

What exactly are you trying to achieve? There may be better approaches.

Michael MacGregor
Database Architect