My client wants to maintain audit at column level. He wants a table which stores all the audits i.e. at column level. I have decided on a table structure. i.e
id,tablename,tablecolumn,primarkey,oldvalue,newvalue,date
But i was wondering how will i check for each column level changes. Do i have to check each one manually like old.columnname <> new.columnname and then add into audit table ?
Is there any other way to do it.
When I was given the same task, I've created several stored procedures for modifying table data and restricted CUD operations on table to force users to use only stored procedures.
SPs were actually performing the action requested (i.e. create, update or delete a row in table) plus adding a line to the audit table for every action.
This might be quite inefficient from DB optimization point of view but I think it gives you the most of auditing as update operation and audit are automatically put in a transaction and update can not be completed without adding an audit entry.
