ASP.NET 3.5 Enterprise Application Development with Visual Studio® 2008: Problem - Design - Solution

12.1. Problem

The key features of the audit trail for the Paid Time Off solution are as follows:

• It enables users to track additions, updates, and deletion of data.

• It enables users to configure how much detail to capture. If they don't care about updates to certain tables, then they can turn the audit trail off so the database does not become bloated.

• The audit trail captures the before-and-after picture of each field when it is updated, which makes tracing changes easy.

There are many patterns out there for audit trails. Some use database triggers that are deployed in the database itself; others add the code only in the data access layer; and still others implement the code in the business layer. A decision also needs to be made regarding how much data is captured. Some systems have a duplicate table structure for every table and when a record is updated in the main table, a copy of the original record — including all its fields — are added to the audit table. Other patterns use a single audit table that keeps track of only those changes made at the field level, as well as additions and deletions. There is no right or wrong way to create an audit trail, as long as the solution you implement meets the user's and/or auditor's requirements.

This chapter will implement auditing in the business layer and use a single table to track all the additions, changes, and deletions of records. Discussed in detail are the requirements of the audit trail for the Paid Time Off solution and how ...