jv (ju***********@hotmail.com) writes:
I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?
Not in a way that I would call painless. You could use something
with dynamic SQL, but that would come with a performance cost, and
you really don't want to spend to much time in triggers, since you
are in a transaction. You can easliy get into locking issues.
It is better - although boring - to type the SQL for each column to
log. Writing a program that generates the trigger code could be an
option to save time.
If you are in for massive auditing, consider using a third-party
product. A trigger-based solution is SQLAudit from Red Matrix.
Lumigent offers Entegra which works from the transaction log.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
