What is an Audit Logging system?

An Audit Logging system is used to provide a history or audit trail of all changes made to the system. These changes can be viewed with a set of simple online screens.

Some applications have the requirement (e.g. to comply with Sarbanes-Oxley) to keep a log of changes made to data within the application, either to subsets of data or the entire database. Incorporated into this requirement is a facility to view the contents of the log to see "who changed what, and when". This facility is commonly referred to as an Audit Trail or Audit Log.

The typical method of implementing this requirement is via database triggers. This method has the following disadvantages:

  • Each table to be audited requires its own table in the audit database. This effectively doubles the number of tables.
  • Each audit table must have the same structure as the table being audited. This means that any change to the structure of the "live" table must be duplicated in the audit table.
  • Each table to be audited requires its own set of database triggers. If there is a separate trigger for INSERT, UPDATE and DELETE you can just imagine the volume of code contained within these triggers.
  • Each audit table has its own screen to view its contents. This means that it is difficult to view updates that span several tables.

Having a separate audit table for each "live" table, having separate trigger code for each table, and having a separate view screen for each audit table is very labour intensive, which is something to be avoided in a RAD environment. The solution implemented within RADICORE was designed with the following attributes:

  • The smallest number of tables possible to hold all audit data. RADICORE has 4 - one for sessions, one for transactions, one for tables and another for fields.
  • The smallest number of screens possible to view all audit data. RADICORE has 2 - a search screen and a detail screen.
  • The smallest amount of effort possible to turn on audit logging for any database table. In RADICORE this is as simple as turning on the Audit Logging? switch in the Data Dictionary.
  • Modifications can be made to any application tables without having to make any corresponding changes in the audit tables.

Even though an Audit Logging facility may not actually be required by the end users of your application, it can be a very useful aid during development as it can very quickly show you the result of any database update. Thus any difference from the expected behaviour can be spotted more easily and corrected sooner rather than later.

The audit logging system supplied with RADICORE is described here.

Published: 09 April 2006