[AccessD] Audit Trails

Hi Max

Exactly my point: 1.3 TB ~1000 * 1.3 GB databases (and how many GB sized dbs do you hold?).
Equals £0.11 or less than a US quarter or €0.15 euro cents per database. Hardly a topic for discussion. 

/gustav


>>> max.wanadoo at gmail.com 19-07-2009 09:33 >>>
When you consider that last week I bought 1.5Tb HD for £110 sterling from PC
World. Plugs straight into my usb port and comes up as 1.3Tb formatted.

I can make copies to my hearts content and never run out of space.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 19 July 2009 07:58
To: accessd at databaseadvisors.com 
Subject: Re: [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