CODEWITHSUNDEEP
mysqlloggingcomparisontriggersaudit
Jas Panesar
Jas Panesar

Reputation: 6639

MySQL Trigger based Audit logging with comparisons

In looking at similar questions like:

Getting trigger to insert changed column values only in audit table

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!

Upvotes: 0

Views: 2305

Answers (1)

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32134

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.

Upvotes: 1

Related Questions