Tracking changes in database tables is an incredibly useful feature–especially for operational data that can change often. Having recently had to implement this feature, I thought I’d share some of the techniques I learned.

Sample Database

First, let’s conceptualize a very simple database consisting of user information (name, date of birth), and e-mails. A user can have more than one e-mail.

We want to track all changes to the FirstName, LastName, and birthdate fields. In addition we want to track when e-mails are added or removed from a user. As we’ll see, these aims are accomplished using two different methods.

My implementation is done in SQL Server 2000 and C#, but any database that supports triggers can be used.

Changes in a Single Table

With this method we want to track the changes to all fields of a table. In our example, we want to know when FirstName, LastName, and birthdate change values in the UserData table.

To accomplish this we need another table to track the history. This table is going to have the exact same fields as UserData, plus a few extra for the change tracking.

Table: UserDataChanges

Now the automated part–adding a trigger to populate this automatically:

CREATE TRIGGER UserDataChangeTrigger ON UserData FOR UPDATE, INSERT

AS    

    IF (UPDATE (FirstName) OR UPDATE(LastName) OR UPDATE(birthdate))    

    BEGIN     

        INSERT UserDataChanges 

            (ChangeTime, ChangeUser, ID, FirstName, LastName, birthdate)

            (SELECT GetUtcDate(), user, ID, FirstName,LastName,birthdate 

                FROM inserted)     

    END     

This trigger will insert a new row into the UserDataChanges table whenever a row in the UserData table is updated or inserted. The IF (UPDATE(FirstName)…. ) is not strictly required in this scenario, but in other cases I did not want a change recorded when certain fields were updated (i.e., you have a field that tracks the last change time of that row, or the number of orders, or any other field that can change frequently and isn’t important to track–you don’t want to create too much noise in this or it will not be useful). The GetUtcDate() and user are SQL Server functions that retrieve the current UTC time and the username of the process that caused the change–very useful for tracking responsibility. The inserted table is created by the server for use by the trigger and contains all the new values.

Changes in a Foreign Key Table

The UserEmails has to be handled differently because there can be multiple e-mails for each user and we can assume they can be added, or removed at will (Remove + Add = Update, so I won’t consider direct updates here).

The solution I landed on was to have a generic event log table that stores manual log entries as well as “special” entries denoting adding or removing e-mails.

Table: UserEventLog

This table can be used for both adding text notes to a user and, by using the EventType field, special events. In our example, we have two events we need to track:

(In code, I’ve made these enumerations)

Next we add a trigger on the UserEmails table:

CREATE TRIGGER UserEmails_EmailAddedTrigger

ON UserEmails

FOR INSERT

AS

 BEGIN

     INSERT UserEventLog(ID, EventTime, EventType, ChangeUser, Notes)

        (SELECT ID, GetUtcDate(), 1, user, '{'+email+'}' FROM inserted)

 END

The value 1 stands for EmailAdded. I’ve added braces around the actual e-mail address to set it apart from regular notes (we’ll see how to integrate everything later).

To handle the deletion of e-mails add another trigger:

CREATE TRIGGER UserEmails_EmailRemovedTrigger

ON UserEmails

FOR DELETE

AS

 BEGIN

     INSERT UserEventLog(ID, EventTime, EventType, ChangeUser, Notes )

        (SELECT ID, GetUtcDate(), 2, user, '{'+email+'}' FROM deleted)

 END

The only things different: FOR DELETE (instead of INSERT), changed the EventType to 2 (EmailRemoved), and the values are taken from the SQL Server-supplied deleted table.

That’s enough to get a pretty good change-tracking system in place, but you’ll still have to build a UI to display it effectively.

Displaying the Changes in the UI

With the above work done, you end up with two types of entities: changes and events. While it would be possible to integrate all functionality into a single event/change table using a lot more logic in the SQL Trigger code, I’m personally more comfortable with the change logic being in my application code. I think this way the database is kept more “pure” and open to changes down the line.

That means we will need to integrate these two types of entities into a single list, ordered by date/time. I’m going to assume the existence of two classes or structs that represent each of these entities. They’ll be called UserChange and UserEvent. I’ll also assume that the lists of each of these are already sorted by time, since that’s trivial to do in a SQL query.

Given that, we need a function that takes both of these lists and produces a sorted, combined list with an easy-to-understand list.

How the function works:

1. Go through both lists, and pick whichever one is next, time-wise.
2. Translate the object into a string/list-view representation of that object.
3. If it’s a UserChange object, compare it to the previous one to figure out what changed.
4. Sort the list in reverse order to put newer items at the top.

Here’s the C# code which I’ve adapted from our production system. Don’t get hung up on the details:

private void FillLog(IList<UserEvent> events, IList<UserChange> changes)

{

    List<ListViewItem> tempItems = new List<ListViewItem>();

    int currentEventIdx = 0;

    int currentChangeIdx = 0;

    eventLogListView1.Items.Clear();

    while (currentEventIdx < events.Count

    || currentChangeIdx < changes.Count)

    {

    UserChange currentChange = null;

    UserChange prevChange = null;

    UserEvent currentEvent = null;

    DateTime changeTime = DateTime.MaxValue;

    DateTime eventTime = DateTime.MaxValue;

    if (currentChangeIdx < changes.Count)

    {

        currentChange = changes[currentChangeIdx];

        changeTime = currentChange.ChangeDate;

        if (currentChangeIdx > 0)

        {

        prevChange = changes[currentChangeIdx - 1];

        }

    }

    if (currentEventIdx < events.Count)

    {

        currentEvent = events[currentEventIdx];

        eventTime = currentEvent.EventDate;

    }

    string dateStr;

    string userStr;

    string eventTypeStr="";

    string notesStr;

    if (changeTime < eventTime)

    {

        dateStr = Utils.FormatDateTime(changeTime);

        userStr = currentChange.UserName;

        notesStr = GetChangeString(currentChange, prevChange);

        currentChangeIdx++;

    }

    else

    {

        dateStr = Utils.FormatDate(eventTime);

        userStr = currentEvent.UserName;

        notesStr = currentEvent.Notes;

        eventTypeStr = currentEvent.EventType.ToString();

        currentEventIdx++;

    }

    if (notesStr.Length > 0)

    {

        ListViewItem item = new ListViewItem(dateStr);

        item.SubItems.Add(userStr);

        item.SubItems.Add(eventTypeStr);

        item.SubItems.Add(notesStr);

        item.ToolTipText = notesStr;

        item.BackColor = (tempItems.Count % 2 == 0) ? 

            Color.Wheat : Color.White;

        tempItems.Add(item);

    }

    }//end while

    eventLogListView1.BeginUpdate();

    for (int i = tempItems.Count - 1; i >= 0; i--)

    {

    eventLogListView1.Items.Add(tempItems[i]);

    }

    eventLogListView1.AutoResizeColumn(0, 

        ColumnHeaderAutoResizeStyle.ColumnContent);

    eventLogListView1.AutoResizeColumn(1, 

        ColumnHeaderAutoResizeStyle.ColumnContent);

    eventLogListView1.AutoResizeColumn(2, 

        ColumnHeaderAutoResizeStyle.ColumnContent);

    eventLogListView1.Columns[3].Width = eventLogListView1.Width - 

    (eventLogListView1.Columns[0].Width +

    eventLogListView1.Columns[1].Width +

    eventLogListView1.Columns[2].Width +10);

    eventLogListView1.EndUpdate();

}

Now we need to define GetChangeString, which figures out the differences in successive UserChange objects and displays only pertinent information.

private string GetChangeString(

    BuoyDataChange currentChange, 

    BuoyDataChange prevChange)

{

    StringBuilder sb = new StringBuilder();

    if (prevChange == null)

    {

        CompareAndAdd(sb, "First Name", 

            null, currentChange.FirstName);

        CompareAndAdd(sb, "Last Name", 

            null, currentChange.LastName);

        CompareAndAdd(sb, "Birth Date", 

            null, currentChange.BirthDate);

    }

    else

    {

        CompareAndAdd(sb, "First Name", 

            prevChange.FirstName, currentChange.FirstName);

        CompareAndAdd(sb, "Last Name", 

            prevChange.LastName, currentChange.LastName);

        CompareAndAdd(sb, "Birth Date", 

            prevChange.BirthDate, currentChange.BirthDate);

    }

    return sb.ToString();

}

And one last helper function which compares two objects and if different appends the change to a StringBuilder object.

private void CompareAndAdd(StringBuilder sb, string field, 

    object oldVal, object newVal)

   {

       if (oldVal == null && newVal == null)

           return;

       if (oldVal == null || !oldVal.Equals(newVal))

       {

           if (sb.Length > 0)

           {

               sb.Append(", ");

           }

           sb.AppendFormat("{0}:{1} -> {2}", field, oldVal, newVal);

       }

   }

In this way you can end up with an automated system that displays all changes in an easy-to-understand format.

Here’s a sample of what our system looks like (click to enlarge):

Other ways to accomplish this? Better ways? Please leave a comment!