Re: loging class

[prev] [thread] [next] [Date index for 2005/07/28]

tom.kirkpatrick@xxxxxxxx.xxx writes:
 > I need a kind of CDBI logging class, ie. one which logs all changes made 
 > to the database to a loging table (within the database).
 > 
 > I'm guessing that this could be done through the use of triggers, but is 
 > there a better way? Or has such a thing been implemented so many times 
 > before that there is a nice class out there on CPAN waiting and ready for 
 > me to use?

Tom -- I've kept an eye out for such a thing, but have never seen a
generalized implementation made available.

Here's a relevant cdbi-talk post that I had saved for future reference.
http://groups.kasei.com/mail/arc/cdbi-talk/2005-03/msg00141.html

 > I'm thinking you might have a log table like so:
 > 
 > log_id        date        table_name        column_name        old_value   
 >   new_value
 > 
 > Which basically keeps a history of the entire database. Is this a silly 
 > idea or might it work?
 > more to the point, is there some code out there I can steal to get it done 
 > quickly!

The following has some ideas, despite lack of Perl context.
http://www.tonymarston.co.uk/php-mysql/auditlog.html

Something that's always kept me from implementing such an approach is
my concern that such logging is too low-level to be truly useful.  For
example, consider a user action that results in the insertion or
update of more than one record in more than one table.  You could also
view this as a transaction, an all-or-none change to the database.
Affecting multiple records across multiple tables is an inevitability
of proper relational design.  Now, your audit tables will contain many
entries detailing each and every change.  Is this useful?  Can you
review the audit trail in a reasonably easy way?  I think it would be
more useful to keep a higher-level log of the effect of the user's
action.  Arguably, logging changes at many levels would be the ideal
approach.  However, logging at any level higher than the absolute
lowest becomes an application-specific exercise that cannot be
generalized, and therefore cannot be a module/class that's useful to
others.

Grateful to hear others' thoughts on this, or pointers to related
discussion.

Kings