[AccessD] Audit Trails

Hi Stuart and Rocky

Why all this trouble? Today disk space cost is very low so why not just create a copy of the record before any change? 
Just follow CRUD:

C. At OnInsert:
  Append a copy of the new record to the audit table.

R. At OnCurrent (if needed which seldom, though sometimes, is the case):
  Append a copy of the current record to the audit table.

U1. At OnCurrent:
  Create a temporary copy of the current record.
U2. At OnAfterUpdate:
  Append the temporary copy to the audit table.
  Append a copy of the updated (now current) record to the audit table.

D1. At OnCurrent:
  Create a temporary copy of the current record.
D2. At OnDelete:
  Append the temporary copy to the audit table.

This is for forms. If you have code that modifies tables, adjust the code to include similar operations. Too much trouble, you may ask? True. Auditing is trouble and use of resources.

The audit table is identical to the table to be audited with the addition of a timestamp, a user id, and an operation code. This allows extensive and fast searching which should be the key requirement to any auditing system (what else?) like "who deleted records between then and now?" and "when were changes made for customer id x?" or "which appends or deletes have been made by user id n?". The result will - without access to the audited table - contain _all_ info, not only a PK or a few changed fields.

When the client whines about potential disk space consumption, tell him that auditing does cost resources including disk space. If this is a true issue - if you may touch the physical limits of the database like the 2 GB limit of JET - place the audit tables in another database - or databaseS like one for each year or month.

/gustav


>>> stuart at lexacorp.com.pg 19-07-2009 01:24 >>>

I certainly wouldn't try to trigger an audit trail at the control level. The logical place is at the 
time you insert, update and delete a record.   

For the insert, you don't need to store any info about the record other than its PK.

For the delete, you need to store all fields for the record whether they are displayed on the 
deleting form or not.

In neither of these cases do you need to iterate through controls.  It's only the Edit audit that 
needs iterate through the fields exposed to controls on the editing form and record which 
ones have changed.

There is a lot to be said tor using three different audit tables for the three different cases.
(That's exactly the way I've done it in the past in SQL Server using triggers).