Audit Trail Package

part of the ArsDigita Community System by Jesse Koontz

The Big Picture

When you have more than one person updating information in a table, you want to record all the values of a row over time. This package gives you (1) a standard way of naming tables and triggers in Oracle, (2) two Tcl procedures (ad_audit_trail and ad_audit_trail_for_table) that helps you display the old values of a row, including highlighting particular changed columns, (3) a Tcl procedure (ad_audit_delete_row) that simplifies the logging of a deleted row, and (4) an example user interface ( audit-tables.tcl, audit-table.tcl, audit.tcl) to retrieve and display audit histories.

Steps for Auditing a Table

We record old information in a separate audit table (see the triggers chapter of SQL for Web Nerds for more explanation of this idea).

We distinguish between the on-line transaction processing (OLTP) tables that are used in the minute-by-minute operation of the server and the audit tables.

Here are the steps to add audit trails:

Reference

Audit columns: Arguments for ad_audit_trail_for_table Arguments for ad_audit_trail Arguments for ad_audit_delete_row

Future Improvements

The ad_audit_trail and ad_audit_trail_for_table procedures could be extended to restore previous values. The restore_url would be a pointer to a script that could restore an old row to the main table. The script would need to query the data dictionary for the columns of the audit and main tables. It might also require the user to confirm if a current record would be overwritten by the restore option.
jkoontz@arsdigita.com