How to keep track of changes to data in a table?

Question

I have a simple question. How can I keep track of changes to a row in a SQL Server table? Here's an example of what I want.

Table: Users

Columns: Name | Address | Username | UserType

Row 1: Christopher | 123 Fake Street | Lover1234 | 1

How can I keep track of what time the user changes "Christopher" to "Robert" or if they change "123 Fake Street" to "124 Fake Street". I need to know the time of the change, as well as the old value that was changed. I would also like to be able to track whether the UserType changes (which is a foreign key), so I need to know how to track foreign key changes.

This could occur in multiple different tables and I would like to keep track of multiple different tables.

Answer 1

There are different options to do this. I will mention two:

Option 1:

Add a column called IsHistory to your table. You will end up with something like this:

Name        | Address         | Username  | UserType | IsHistory
------------+-----------------+-----------+----------+----------
Christopher | 123 Fake Street | Lover1234 | 1        | 0

Then when you update the record, change the IsHistory value on the old record to 1, and then add a new record with the updated information, with the IsHistory record to 0. You will end up with something like this:

Name        | Address         | Username  | UserType | IsHistory
------------+-----------------+-----------+----------+----------
Christopher | 123 Fake Street | Lover1234 | 1        | 1
Robert      | 123 Fake Street | Lover1234 | 1        | 0

Option 2:

Add a log table, where you can have the following:

LogID | Entity | ActionType | Description | OldDataXML

Then, each time that an update occurs, insert a record here.

• LogID is the PK
• Entity is the table that was affected by the update
• Action Type is the action taked on the Entity (INSERT, UPDATE, DELETE)
• Description is a brief text explaining the change
• OldDataXML is the data that was on the record before the changes in XML.

For example:

<User Name="Christopher" 
      Address="123 Fake Street" 
      Username="Lover1234" 
      UserType="1">
</User>

In this option, the record on the Users table will be always the latest one, and on the log table you will have the information of the changes.

Hope this can help you.