|
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 |
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
