/>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
>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> )
INSERT INTO <tbl>v VALUES ( <param list>, NOW(), '2037-12-31' );
CREATE PROCEDURE tbl_update( <param list, one per coll>, pstartdate
TIMESTAMP, penddate TMESTAMP )
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' );
CREATE PROCEDURE tbl_delete( psid INT )
SET stopdate = NOW()
WHERE <key col name> = OLD.<key col value> AND stopdate = '2037-12-31';
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.
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