For many companies and government agencies, accurate Audit and History information is important and in some cases, mandated.

For the purposes of this article, the following definitions are presented:
• Audit – which record was inserted, updated, or deleted, when, and by who
• History – storage of earlier values from records that have changed

Considerations for Audit and History System Designs
In designing a comprehensive Audit and History (A/H) system, a number of considerations must be made.

Among them:
• Is this A/H functionality being built into the system early in the design process or is an existing system being retrofitted with the A/H functionality?
• For what data should A/H  be maintained?  Partial?  Complete?
• Will the history information tie into systems external to the databases/file systems under direct control of the immediate system?
• Where should the functionality be located – client, midtier, or database?
• Is A/H important enough that transactions should fail if the A/H fails?
• If the A/H functionality is based in the database, will the client log in to the database with the user’s own id or one or more generic accounts?
• How long should A/H information be stored?

Like many aspects of software development, compartmentalization of functionality is a preferred approach.  There are many different locations in a multi-tiered environment that can house the logic necessary for A/H.  The location chosen could be determined by direct needs of the project with little flexibility.

Locating Audit and History Functionality on the Database Layer
In situations where the flexibility exists to house the A/H functionality directly in the database, this method allows complete distinction of the business logic of the application from that of the A/H.  The main criteria of housing the A/H functionality directly in the database is that the database have either table trigger capabilities or procedure capabilities, the former, a much preferred method.

Advantages of Audit and History in the Database Layer
• Clear distinction between the business logic of the application and that of A/H.
• Single location for A/H functionality which simplifies maintenance of each the A/H functionality and the application layer.
• Multiple applications’ use of the database without duplication of A/H functionality on the application layer.
• Simple retrofit of A/H functionality to databases for which complicated application layers already exist.

Specific Considerations for Locating Audit and History Functionality on the Database Layer
A key issue to consider in using the database layer for Audit and History is that the database must know which user is performing transactions.  Most applications that use a midtier also use generic accounts to interact with the database after a user has authenticated properly to the midtier.  Therefore, a call from the midtier to the database to identify the user prior to performing any data transactions must take place.  This information can be of any format as long as it identifies the user in a way that makes sense to the database (primary key from a user table, login name, etc).  In an application that was designed by Platinum Solutions, we located this call within a base class so the call happened automatically when this base class was extended for specific application functionality.

The coding of the A/H triggers and procedures can be tricky when first attempted.  In some cases if, within a single transaction, data is entered into one table and then the primary key of that table is to populate another table, the database is unable to use the primary key data on the second table.  The database may be able to return the value of the key but is unable to insert it into the second table as a foreign key – resulting in a deadlock.  This can be circumvented by using temporary storage areas.  In the most recent project, collections were used to store this information until it could be transferred to the history table.

Conclusions
Using the Database Layer for Audit and History functionality is a way to separate the business logic (which is often being modified and expanded in scope) from the logic of the Audit and History.  With the correct implementation, this methodology can prove to be a simple, efficient, and flexible way to keep accurate Audit and History information in a relatively maintenance free manner.