7

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.

Professed3376
  • 411
  • 2
  • 7
  • 19
  • 2
    You can start with change tracking: http://msdn.microsoft.com/en-us/library/bb933875.aspx. – Gordon Linoff Dec 31 '14 at 19:58
  • you have to implement UserActivityTracking in your application, so every add/update/delete, you have track that – HaveNoDisplayName Dec 31 '14 at 19:58
  • 2
    Change tracking, change data capture, auditing, triggers, or forcing data manipulation through stored procedures and writing your own logging. If you need to who changed what value from what to what and when, the first two solutions only provide (different) portions of that information. – Aaron Bertrand Dec 31 '14 at 20:43

2 Answers2

7

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.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
scubaFun
  • 1,269
  • 1
  • 15
  • 26
  • Another option is to simply not do updates at all and instead just do inserts as the data changes. This is similar to the Type II SCD model in dimension data warehouse design. – siride Dec 31 '14 at 20:19
  • I really like the first option. It seems simple compared to creating a whole new table for every table in order to track changes. Are there any downsides to option 1? – Professed3376 Dec 31 '14 at 20:33
  • You can combine the first option with what @siride mentioned too! :) – scubaFun Dec 31 '14 at 20:34
  • 1
    If I went with option 1, how would I handle foreign keys that reference the original row? Change them to reference the new update d row? Or swap the primary key from the old row to the new row? – Professed3376 Dec 31 '14 at 21:44
  • The best option is to update the foreign keys with the new value. – scubaFun Jan 01 '15 at 01:59
2

ChangeDataCapture is built into SQL Server 2008 R2 and can handle this (http://technet.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx)

The alternative that I have usually used is very similar to what @scubaFun had in his answer which is to create a logging table that mirrors the original table, plus a change type column and changed on (and whatever else you want) and then use triggers on the main table to populate the logging table.

Earl G Elliott III
  • 425
  • 2
  • 3