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

Question

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:

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

@p.marino : I want to do it from application instead of using triggers. I've read about the pros and cons of using triggers in one of the above mentioned links. For me it's also simpler to do it in application. — understack, Apr 05 '11 at 06:27

score 4 · Accepted Answer · answered Apr 05 '11 at 06:26

4

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.

answered Apr 05 '11 at 06:26

p.marino

6,244
3
25
36

Finding actual user identity is not a problem in my case. – understack Apr 05 '11 at 06:33
Then I really think that triggers are the way to go, unless mySQL has some obscure problems with them (not very familiar with it, my bad). – p.marino Apr 05 '11 at 07:14

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

1 Answers1

Linked