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,