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

Question

I would like to write the changes made to all fields of the table to another table. This will be used to keep a history of all changes made to this table (table of main change tables).

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

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

How can I go through all the fields that test the change without hard coding all the fields?

As you can see from my questions, the sample code will be very important.

I noticed that SQL Server 2008 introduced a new Change Data Capture (CDC) feature. (Here is a good Channel9 video on CDC). This is similar to what we are looking for, except that we are using SQL Server 2005, we already have the layout of the log table in place, and also register the user who made the changes. It’s also difficult for me to justify the record before and after the image of the whole record, when one field can change.

Our current log file structure has a column for the field name, old data, new data.

Thanks in advance for a nice day.

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

• You can create a trigger for this. See How to check sql server data changes .

• You can use triggers to record data changes in the log tables. You can also purchase the log explorer from www.lumigent.com and use it to read the transaction log to see what the user has done. However, the database must be fully restored for this option.

Updated 12/23/08:. I also need a clean way to compare what has changed, and it looks like the flip side of the PIVOT that I discovered in SQL is called UNPIVOT. Now I'm leaning towards a trigger using UNPIVOT in the INSERTED and DELETED tables. I was curious if this has already been done, so I am looking at a search for univot deleted insert .

• Posting Using the update function after a trigger had several different ideas, but I still believe that UNPIVOT will be the route.

Answer 1

Pretty late, but hopefully it will be useful for other readers ...

Below is a modification of my answer, which I posted last week in a similar topic.

The short answer is that there is no “right” solution that fits all. It depends on the requirements and the system being tested.

Triggers

• Advantages : relatively easy to implement, great flexibility in what is checked and how audit data is stored, because you have full control
• Disadvantages : it gets messy when you have a lot of tables and even more triggers. Maintenance can become difficult if no third-party tool exists for this. In addition, depending on the database, this may affect performance.

Creating Audit Triggers in SQL Server
Make changes to the database table using a trigger

Cdc

• Advantages : very easy to implement, supported initially
• Disadvantages : available only in the corporate version, not very reliable - if you change the scheme, your data will be lost. I would not recommend this to maintain a long-term audit.

Reading transaction log

• Advantages : all you have to do is put the database in full recovery mode and all information will be saved in the transaction log.
• Disadvantages . This file requires a third-party magazine reader.

Read the log file (* .LDF) in SQL Server 2008
SQL Server Explorer / Transactional Analyzer

Third Party Tools

Ive worked with several audit tools from ApexSQL, but there are also good tools from Idera (compliance manager) and Krell software (omni audit)

ApexSQL Audit - A trigger-based audit tool. Generated and controls audit triggers.

ApexSQL Log - Allows you to audit by reading a transaction log

Answer 2

In 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 #TempTable, for example, inserting these entries in the log table.

As a side note, this is a very simple way to capture the value of an identification field.

Answer 3

You can use Log Rescue . This is the same as in Log Explorer, but it is free.

He can view the history of each row in any tables with information about user registration, actions and time.

And you can undo any version of a row without setting the database in recovery mode.