Tracking Database Record Changes at the Field Level

One of the projects that I’m currently working on is to track change requests including enhancements and software defects. While I was working on the project, I realized that it would be necessary to keep track of who changed what and when.

In the past, I’ve used audit fields such as created_by, created_on, last_updated_by, and last_updated_on to keep track of who created the record and when they created it. Those fields also told me who the last person to edit the record was. Those techniques were implemented using triggers and default values for the record creation event.

However, the biggest flaw with that technique is that it only shows you the last person to edit the record. It doesn’t even say what they edited.

In tracking change requests, I’ll need to be able to figure out who closed the item or changed the severity in case there’s a dispute or (more likely) someone did it by accident and now we need to fix it.

One scenario that could play out is as follows:

1. A bug is submitted to the system by Bob (user).
2. Joe (developer) changes the resolution from ‘Open’ to ‘Fixed’ (a field which Bob can’t edit).
3. Bob goes back and updates the title (a field that the request owner/submitter can edit).

Now the record has the last_updated_by value of “Bob” and nobody knows which developer changed the resolution from ‘Open’ to ‘Fixed’.

Next, Bob asks why it was closed because it still isn’t fixed. Jack (project manager) edits the record changing the status from “Fixed” back to “Open”.

To accomplish this, I created a table named “Change_Log” with columns to capture which field was updated, original value, new value, who updated it, and when it was updated.

(Table definitions below are not complete and are only used for reference purposes only. Foreign keys, unique keys, constraints, etc. are not shown to reduce the size of the post.)

Primary Data Table Definition

CREATE TABLE [dbo].[Change_Request](
    [Request_Id] [int] IDENTITY(1,1) NOT NULL,
    ...
    [Resolution_Status_D] [varchar](50) NULL DEFAULT ('Pending'),
    ...

    [Last_Update_By] [varchar](50) NULL,
    [Last_Update_On] [datetime] NULL,

    ...

Change Log Table Definition

CREATE TABLE [dbo].[Change_Log](
    [Change_Id] [int] IDENTITY(1,1) NOT NULL,
    [Request_Id] [int] NOT NULL,
    [Column_Changed] [varchar](50) NOT NULL,
    [Original_Value] [varchar](255) NOT NULL,
    [New_Value] [varchar](255) NOT NULL,
    [Created_By] [varchar](50) NOT NULL,
    [Created_On] [datetime] NOT NULL,
    CONSTRAINT [PK_Change_Log] PRIMARY KEY CLUSTERED
    ([Change_Id] ASC)) ON [PRIMARY]

Trigger Definition

CREATE TRIGGER [dbo].[Change_Request_OnUpdate]
   ON  [dbo].[Change_Request]
   AFTER UPDATE
AS
    --local vars
    DECLARE @updated_on DateTime
    DECLARE @updated_by varchar(255)
    DECLARE @orig_value varchar(255)
    DECLARE @new_value varchar(255)

    --defaults
    SELECT @updated_on = getdate()
    SELECT @updated_by = system_user    

    --check Resolution_Status_D for updates
    IF UPDATE(Resolution_Status_D)
    BEGIN
        SELECT @orig_value = (Select COALESCE(Resolution_Status_D, '') FROM deleted)
        SELECT @new_value = (Select COALESCE(Resolution_Status_D, '') FROM inserted)

        -- compare old and new, don't inserted same value if not changed
        IF NOT (@orig_value = @new_value)
        BEGIN
            insert into Change_Log
                (Request_id, Column_Changed,
                Original_Value, New_Value,
                Created_By, Created_On)
            SELECT
                inserted.Request_id, 'Resolution_Status_D',
                @orig_value, @new_value,
                @updated_by, @updated_on
             FROM inserted
        END -- IF NOT (@orig_value = @new_value)
    END -- IF UPDATE(Resolution_Status_D)

The above trigger will execute after the record in our primary data table has been updated. First, I declare 4 variables that we will use for the Change_Log table entries.

Since I want to group the Change_Log entries by the user and time (so I can group several edits into one display/output grouping), I need to have the update time and updated by values to be the exact same, so I set the @updated_on and @updated_by values in the beginning of the code block.

Next, I check to see if the column named “Resolution_Status_D” was updated. If so, then I get the original value from the temporary table named deleted and the new value from the temporary table named inserted.

Finally, I compare the two values and if they are different, then I insert a record into our Change_Log table.

Now, the output from our scenario would look like:

• Joe changed “Resolution_Status_D” from “Pending” to “Fixed” (9/4/2007 11:05 AM).
• Bob changed “Title” from “Very vague title” to “More specific title” (9/4/2007 11:17 AM).
• Jack changed “Resolution_Status_D” from “Fixed” to “Open” (9/4/2007 1:34 PM).

Above output assumes that I use the same block of code to trap for changes to the “Title” column as well as the “Resolution_Status_D” column.

My Related Posts: Technology, Programming, Database