I'm attempting to develop an audit trail/tracking solution for an existing database written in PLSQL/PHP - however I'm still unsure as of yet on an easy (to implement and maintain) solution for tracking changes to fields/values. For instance, the project tracking portion of the DB APP tracks over 200 fields and ideally I'd like a nice way to show a history of changes, such as:
5/10/2010 - Project 435232 updated by John Doe
Changed Project Name (Old: Test Project; New: Super Test Project)
Changed Submission Date (Old: 5/10/2010; New: 5/11/2010)
Changed Description (Old: This is an example!; New: This is a test example)
Essentially for each field (db column) it would output a new line to show the old/new values. So far my current idea is saving the current version of the data to a temporary table, updating the primary table with the new data then loading each row into an array and doing an array compare to determine the differences. This seems a bit convoluted, and if there is an easier method I'd love to know it.
Any ideas or suggestions are much appreciated!