CODEWITHSUNDEEP
mysqldatabasedatabase-design
understack
understack

Reputation: 11580

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

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

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

But I've got few basic doubts in implementing that.

In my case, multiple columns in a row could be updated at the same time, so is following the correct way to implement:

  1. Find out type of operation (INSERT/UPDATE/DELETE)
  2. Find out columns which are getting updated
  3. Read the full row before updating
  4. Insert one row in Audit table for each column being changed with old and new value (along with other details)
  5. update the table

Upvotes: 6

Views: 2313

Answers (1)

p.marino
p.marino

Reputation: 6252

Assuming you are using a sufficiently recent version of mySQL I would use triggers, personally.

Assuming they work more or less as the ones I am familiar with in other products (e.g. Oracle) your problem becomes simpler, in the sense that you put an "update" triggers on the row and use it to update the audit table for each field you are interested in.

Possible caveat: if your application logs on the DB as just one user (a common approach if you use connection pooling, for example) it may be tricky to log the actual user identity.

Upvotes: 4

Related Questions