How to track changes in multiple columns in a database table for audit purposes?

Question

This question has been answered on SO several times: here , here, and external links here and here .

I understand the approaches described above, and I plan to use this approach.

But I have a few fundamental doubts about its implementation.

In my case, several columns in a row can be updated at the same time, so you should follow the correct implementation method:

• Find out the type of operation (INSERT / UPDATE / DELETE)
• Find out which columns are being updated.
• Read the full line before upgrading
• Insert one row in the audit table for each column modified by the old and new value (along with other information).
• refresh table

Answer 1

Assuming you are using a fairly recent version of mySQL, I would use triggers in person.

Assuming that they work more or less like the ones that I am familiar with in other products (for example, Oracle), your problem becomes simpler, in the sense that you put update triggers in a row and use it to update the audit table for each one of interest you fields.

Possible caveat: if your application is registered in the database as only one user (for example, the general approach, if you use the connection pool), it may be difficult to write the real user ID.