Audit Trail: best design technique?

Audit Trail: best design technique?

Post by Michael Lo » Mon, 04 Nov 1996 04:00:00

I developed a stand-alone database for a pharmaceutics company using
Delphi/dBase IV tables that generates an audit trail for all changes
made and let me tell you it was a major pain in the ass.  For every
table that exists, I created a corresponding audit trail table that
stores both old and new values along with a corresponding sequential
audit trail number.  This number is stored in another table along with
information about who changed the record.  On top of all this, I have
another table which stores a record for each FIELD changed and contains:
(AuditNumber,FieldName,OldValue,NewValue).  This allows for relatively
easy reporting.  However, it's a pain and I'm not sure it's the
best/only way to do it.

Has anyone else created databases that need audit trail/change control
logs and if so, would you mind sharing with us/me how you designed it?

Thanks in advance,
Mike Low

Parsippany, NJ


1. How does one design audit trails ?

I have designed a database app that uses about 14 tables. I want to build in
the ability for the app to log all additions/modification/deletions by time.

I have heard that one can create mirror tables and simply dump the transaction
to the table and mark it with a timestamp, userid and flag to denote whether
it is an addition modification or deletion.

There are two problems with this.

        1) The number of tables basically doubles.

        2) Lots of disk space is wasted

Does anyone know of any other approaches and the pros/cons ?

In fact, what is the best way you know to design an audit trail.

I am not using SQL, but am using Paradox 5.0 tables.

2. Complie error with from (

3. Audit Trails, Database Design

4. Can anyone point me to some ADO (5) multi-user network connectivity examples/

5. Best way to implement an Audit Trail

6. P/A-Career Position/Mich

7. Audit-trails, audit-server

8. InterDev and VSS in .NET programming

9. Audit Trail Function

10. Transaction Log as Audit Trail

11. Audit Trails in SQL Server?

12. Audit Trail

13. Trigger and Audit Trails