List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 21 2006 7:26pm
Subject:Re: Audit trail
View as plain text  
Chris

/>I would like to create an audit trail for one table in my DB.  Users 
will login to my
 >web site and be able to enter and edit information, I want to keep a 
record of what
 >changes are made by what user.  These users will be web site users and 
not actual
 >MySQL users.  Is there an easy method in MySQL to do this, or do I 
just need to
 >write code to track any changes as they are entered?
/
It can be done with SQL alone using what's often called point-in-time 
architecture (PITA). To the table that needs auditing, add startdate and 
stopdate timestamp columns, and adopt the conventions that (i) a 
stopdate is understood uninclusively (eg if your hotel stay was 8-10 
Aug, it was 2 days not 3),  (ii) 'now' means a stopdate of 2037-12-31 
(approx the largest possible timestamp value), and (iii) apart from 
stopping in 2037 meaning now, future data is not allowed.

The rule is that _nothing is ever deleted_, so an insert always sets 
startdate=now() and stopdate=2037-12-31, an 'update' becomes an update 
plus an insert, and a 'delete' becomes an update, as in these sprocs:

CREATE PROCEDURE tbl_insert( <param list, one per col> )
BEGIN
  INSERT INTO <tbl>v VALUES ( <param list>, NOW(), '2037-12-31' );
END;
 
CREATE PROCEDURE tbl_update( <param list, one per coll>, pstartdate 
TIMESTAMP, penddate TMESTAMP )
BEGIN
  UPDATE <tbl>
  SET enddate = NOW()
  WHERE <key col name> = <key col value> AND stopdate = '2037-12-31';
  INSERT INTO state_ttv VALUES ( <param list>, NOW(), '9999-12-31' );
END;

CREATE PROCEDURE tbl_delete( psid INT )
BEGIN
  UPDATE <tbl>
  SET stopdate = NOW()
  WHERE <key col name> = OLD.<key col value> AND stopdate = '2037-12-31';
END;

Then a query to find the present state of affairs asks for rows where 
stopdate=2037-12-31, a query for what the table looked like on 1 Aug 
2006 asks for startdate<=2006-8-1 and stopdate>2006-8-1, and so on.

PB

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.4/424 - Release Date: 8/21/2006
Thread
Audit trailChris W21 Aug
  • Re: Audit trailChris Knipe21 Aug
  • Re: Audit trailPeter Brawley21 Aug