I realize the approaches referred to in above threads and that i intend to use this approach.
But I have got couple of fundamental doubts in applying that.
During my situation, multiple posts consecutively might be up-to-date simultaneously, same with following the right way to implement:
- Discover kind of operation (Place/UPDATE/Remove)
- Discover posts that are getting up-to-date
- Browse the full row before upgrading
- Place one row in Audit table for every column being transformed with old and new value (together with other particulars)
- update the table
Presuming you're utilizing a sufficiently recent version of mySQL I'd use triggers, personally.
Presuming they work pretty much because the ones I'm acquainted with in other items (e.g. Oracle) your condition becomes simpler, meaning that you simply put an "update" triggers around the row and employ it to update the audit table for every area you are looking at.
Possible caveat: in case your application logs around the DB as only one user (a typical approach if you are using connection pooling, for instance) it might be tricky to log the particular user identity.