Audit Trail: best design technique?

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
ditech_at_internection.com
Parsippany, NJ Received on Sun Nov 03 1996 - 00:00:00 CST