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

Quite late but hopefully it will be useful for other readers…

Below is a modification of my answer I posted last week on a similar topic.

Short answer is that there is no “right” solution that would fit all. It depends on the requirements and the system being audited.

Triggers

• Advantages: relatively easy to implement, a lot of flexibility on what is audited and how is audit data stored because you have full control
• Disadvantages: It gets messy when you have a lot of tables and even more triggers. Maintenance can get heavy unless there is some third party tool to help. Also, depending on the database it can cause a performance impact.

Creating audit triggers in SQL Server
Log changes to database table with trigger

CDC

• Advantages: Very easy to implement, natively supported
• Disadvantages: Only available in enterprise edition, not very robust – if you change the schema your data will be lost. I wouldn’t recommend this for keeping a long term audit trail

Reading transaction log

• Advantages: all you need to do is to put the database in full recovery mode and all info will be stored in transaction log
• Disadvantages: You need a third party log reader in order to read this effectively

Read the log file (*.LDF) in sql server 2008
SQL Server Transaction Log Explorer/Analyzer

Third party tools

I’ve worked with several auditing tools from ApexSQL but there are also good tools from Idera (compliance manager) and Krell software (omni audit)

ApexSQL Audit – Trigger based auditing tool. Generated and manages auditing triggers

ApexSQL Log – Allows auditing by reading transaction log

Answer 2

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.