Log changes made to all fields in a table to another table (SQL Server 2005)

Question

I would like to log changes made to all fields in a table to another table. This will be used to keep a history of all the changes made to that table (Your basic change log table).

What is the best way to do it in SQL Server 2005?

I am going to assume the logic will be placed in some Triggers.

What is a good way to loop through all the fields checking for a change without hard coding all the fields?

As you can see from my questions, example code would be veeery much appreciated.

I noticed SQL Server 2008 has a new feature called Change Data Capture (CDC). (Here is a nice Channel9 video on CDC). This is similar to what we are looking for except we are using SQL Server 2005, already have a Log Table layout in-place and are also logging the user that made the changes. I also find it hard to justify writing out the before and after image of the whole record when one field might change.

Our current log file structure in place has a column for the Field Name, Old Data, New Data.

Thanks in advance and have a nice day.

Updated 12/22/08: I did some more research and found these two answers on Live Search QnA

1. You can create a trigger to do this. See How do I audit changes to sq​l server data.

2. You can use triggers to log the data changes into the log tables. You can also purchase Log Explorer from www.lumigent.com and use that to read the transaction log to see what user made the change. The database needs to be in full recovery for this option however.

Updated 12/23/08: I also wanted a clean way to compare what changed and this looked like the reverse of a PIVOT, which I found out in SQL is called UNPIVOT. I am now leaning towards a Trigger using UNPIVOT on the INSERTED and DELETED tables. I was curious if this was already done so I am going through a search on "unpivot deleted inserted".

1. Posting Using update function from an after trigger had some different ideas but I still believe UNPIVOT is going to be the route to go.

Answer 1

Under SQL '05 you actually don't need to use triggers. Just take a look at the OUTPUT clause. OUTPUT works with inserts, updates, and deletes.

For example:

INSERT INTO mytable(description, phone)
OUTPUT INSERTED.description, INSERTED.phone INTO #TempTable
VALUES('blah', '1231231234')

Then you can do whatever you want with the #TempTable, such as inserting those records into a logging table.

As a side note, this is an extremely easy way of capturing the value of an identity field.

Answer 2

You can use Log Rescue. It quite the same as Log Explorer, but it is free.

It can view history of each row in any tables with logging info of user, action and time.

And you can undo to any versions of row without set database to recovery mode.

Answer 3

Chris: the MSSQL docs say OUTPUT cannot be used to capture identity fields, as the output is generated before the insert is actually performed and the identity value is chosen.