There are times when we will need to keep track of changes made to data stored in a database. There are several strategies/methods for doing this. We will outline some of the more simple options available to use here.

Option 1 - Maintain all Live Data

With this option the data is never updated or deleted in a table. Instead each table has a field which indicates whether the record is a current one or an old version.

A user table under this scenario might look like this:

In this scenario Janes’ surname has changed from Smith to Bloggs. Instead of deleting or updating the record, the deleted flag was set, and a new record was inserted.

There are however some downsides to this approach:

• Any system accessing this data needs to be aware of how it works.
• If there are a lot of updates on many records the table will grow very large.
• Selecting from this table becomes more complex.

Option 2 –Create a Shadow Table for each Live Table

For this option we need to create a dedicated audit trail table for each live data table we want to keep track of. We then add 3 triggers to the Live Table which will insert records into the audit trail table for every insert, update and delete.

If we had this in place for the previous scenario our audit trail table might look like this:

The advantage of this strategy is that the live table will stay small and any program that accesses it will not be affected.

Some disadvantages are:

• The audit trail table will grow quite large.
• If there are small updates to the records it leaves a lot of redundant data.
• Any change to the table structure requires a corresponding change to the audit trail table and the triggers.
• You can end up with a large amount of audit trail tables.

Option 3 - Generic Audit Trail Table (Records the Table & Amended Field)

If we want something a bit simpler we can use a single table to record all the changes.

In this scenario our triggers just write the old and new values of a changed field. Changes to any table get written to the same audit trail table. This might look like the below table:

The advantage of this method is that only the altered data is recorded. We do not need to save the whole record each time. It also keeps all our changes in one table so they can be retrieved easily.

Some drawbacks to this approach: 

• This table will become very large over time.
• It is not suitable for all data types as everything would needs to be converted to text.

Option 4 - Add Custom Code to the Application

If the data is being maintained by an application that was developed in-house then it will be possible to add code to that application that will write to the audit trail directly. This removes the need for triggers. It will also allow us greater control of what is recorded and when.

The downsides to this approach:

• The application will now be more complicated to maintain
• Any changes to the data made outside of the application will not be tracked.

Hopefully some of the above options will help you to keep track of changes made to data where applicable.

At Dataworks we enable the perfect hybrid of configurable off the shelf toolsets and custom software development to deliver innovative solutions to match your specific business process requirements. This ensures we are the best at what we do.

If you would like to discuss how we can use our experience and expertise to deliver real benefits toyour business please contact us today on 051 878555 or email info@dataworks.ie

Image courtesy of David Castillo Donimici at FreeDigitalPhotos.net