Keep track of database column value changes

I am working on a web based application using Zend framework.

I am looking for a way to keep history of updates made to a bunch of columns.

Use-case: User can add an item with 5 properties. T开发者_开发技巧he user is allowed to update the 5 properties. I need to track all the updates he makes to those 5 properties.

On way I thought of was adding a new table named log which stores the old value, new value, column name, itemID and timestamp.

What is the best way to do this? Are there any existing methods/options/examples ?

Thanks,

What you're probably looking for is an audit log. You can create one using Triggers on your MySQL database.

An example of how to do this is here: http://ronaldbradford.com/blog/auditing-your-mysql-data-2008-07-15/

In your comment below you added that the database is not 'fixed'. In that case, you'd have to replicate any alters to the table so that they are applied to the audit table in such a way that any newly added columns are added to the log as well.

You can consider logging this in your application (as your tags chosen for this question seem to suggestion) - but keep in mind that this means there can be situations where your log does not provide the complete answer. Triggers, stored procedures and any manual interventions in the database will not be logged... so be careful if you choose to go down that path.

Triggers are the most common way to do auditing and the only really reliable way to capture what was done whether done from the user interface or elsewhere. They do vary by database in how they would be written though. If you know the possible types of database backends you will support, you could write separate triggers for each.

If you must handle this without triggers, then your best bet is to have a process that writes to the audit table as well as makes the update change. It might be complex enough to warrant a stored proc called by the Zend framework rather than relying on the framework itself to do. (I'm not familair with Zend so I don't know if this is something that could be set up, I know a stored proc could handle this.)

Here is a better one.. See Pop On the Audit Trail

I just created a new table called it Comp_Hist_Log and then defined the old data in the BEFORE UPDATE hook $oldData = $array('fieldname1', 'fieldname2')

Then at the AFTER_UPDATE hook in my database gui hook file.. I added this code

sql("INSERT INTO Comp_Hist_Log (Com_Rec_Id, old_data, new_data, ChangedDate, ChangedBy)

VALUES('{$data['Record_Id']}', '{$oldData}', '{$messageData}', '{$data['LastUpdated']}', '{$memberInfo['username']}')", $eo); return TRUE;

Hope it helps.. it does work.