MySQL Trigger based Audit logging with comparisons

Question

In looking at similar questions like:

http://stackoverflow.com/questions/740182/getting-trigger-to-insert-changed-column-values-only-in-audit-table

http://stackoverflow.com/questions/617225/audit-logging-strategies

I would like to take this one step further and compare the updated data to see if it actually has been updated... I'm not sure if this is ideal (performance wise).

Background:

We have some data that is periodically updated by the user.

When this data is updated, I would like to compare what they submitted with what is already there and what they submitted, to make sure there has been a change.

If there has been a change, proceed to write an audit entry to the central audit history table outlining the fields changed.

Thoughts/ideas? I did my best to search but didn't have a lot of luck. I'll be happy to delete this question if it's a duplicate.

Thanks!

Answer 1

You could write a BEFORE UPDATE trigger where you use the OLD and NEW aliases in your trigger to compare the values that were available in the record before the update with the values you are updating with. Depending on the result of the comparison you can write an entry to your audit table.