[Tfug] RDBMS audit trails?

[Claude Rubinson]

On Thu, Jul 31, 2008 at 10:01:53AM -0700, Bexley Hall wrote:
> Hi, Claude,

> > > I'm just looking for entries like:
> > > 
> > > record #, who, when
> > 
> > It's trivial to set up triggers to do such a thing but
> > by doing so you're doubling your number of transactions.
> 
> Yes.  But, I figure I could treat the "log" table as a
> FIFO and limit its size in the trigger code.  I had
> assumed something like this would be something that
> users would *want* -- even if only for testing
> purposes -- hence the question.

Limiting the size of a table isn't a problem.  Just include a datetime
stamp and add an INSERT/UPDATE trigger that conditionally deletes the
oldest records.

> > If you have a low number
> > of transactions, a decent RDBMS won't sneeze at this. 
> > But it's ugly,
> > not particularly scalable, and certainly not a "best
> > practice."
> 
> So, for applications that inherently *demand* an audit
> trail (e.g., banking, some medical, etc.) you rely on
> the application to implement the audit trail "reliably"?

Much more so than coding it myself.  To code it oneself undermines the
whole point of an RDBMS which is to be able to restore the db back to
a consistent state at any point in time.  The transaction log records
*everything* that happens to the db, not just what you record.
Definitely use the built-ins if integrity and reliability is what
you're looking for.  (And, along the same lines, don't even consider
MS Access, MySQL, or any other pseudo-RDBMS.)

Claude