Re: Database Design: Maintain Audit Trail of Changes

On Thu, 3 Jan 2013, Bèrto ëd Sèra wrote:

> if it's a strict legal requirement you may want to enforce it with a
> trigger system, so that each time a record is inserted/updated/deleted
> you create an exact copy of it in a historical table, that has the
> original record plus data about who performed the operation, when,
> from which IP, maybe a comment field, etc. So your actual table
> remains limited in size and it's performing well, while the size
> problem is local to the audit logs.

Bèrto,

That's in line with Adrian's suggestion and certainly worth doing. It's
not a required legal requirement but provides the company (and potential
investors) with assurance that data have not been manipulated.

> You also want to use triggers to disable updates and deletes on this
> historical table, for a matter of additional security (you might end up
> needing a procedure to trim it, however, if it grows out of affordable
> bounds).

Yes, the history table will be read-only to all users; writing done by
triggers only.

Much appreciated,

Rich