[Tfug] RDBMS audit trails?

[Bexley Hall]

> > > > 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.

I figured on being even cruder -- just delete the "first"
record after adding the "last" record.  (i.e., initialize
the table with N empty records -- where N is the depth
of the FIFO you want to preserve)
 
> > > 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.)

I know Oracle has support for providing a (detailed)
audit trail.  I was hoping PostgreSQL would have
similar (given time, I suspect they will -- since
chasing Oracle seems to be one of their goals)