View Full Version : Maintaining an audit log

02-26-2008, 01:26 AM
I would like to maintain an audit (change) log for changes to my database. Along the idea of user xxx changed price1 from $yyy to $zzz on ddd/mm/yy

There are about 20-30 fields involved. The only two ways that I can think of to have an audit log are to a) have a hidden copy on the forms of the original value and compare hidden to user entered or b) make a temp copy of the DB record before update and compare it to the new record.

Is there a more practical way of maintaining an audit log? This seems like a huge amount of over-head for this task.


02-26-2008, 07:14 AM
There's a rather simple way, but (assuming you're using MySQL) you'll need version 5.02+ and your tables will need to be type Innodb. If those two things are true, you can use triggers. A trigger is an action that happens every time some action occurs on the table. For example, you can set a trigger so if a row is updated in a table, then a row is inserted into an audit table showing what got updated.

More info:


03-02-2008, 06:15 AM
Are there any other techniques for accomplishing this? The server I am using is still on PHP 4.

03-02-2008, 06:19 AM
You mean MySQL4? The version of PHP doesn't matter.