I have a requirement to log all changes in a table. I have gone through many articles in SO on data audit logging. All seem to be complex to me.
I plan to keep a copy of the table with all before update and deleted data. Is this a good idea?
If we are talking about only one table then yes. If there are several tables then maybe. If there are a lot of tables you need to audit then no.
Reason is that the more duplicate tables you have to maintain the more complex it becomes. Also, reporting from many tables may turn out to be a difficult task.
Here are couple other ideas:
Two table design for storage: Idea is to keep details about transaction in one table (user, host machine, transaction time, table name, database name, etc) and data changes in second table (old values, keys and such)
Third party tools: There are several tools that can provide auditing at a different level . ApexSQL Audit is a trigger based auditing tool, ApexSQL Log is more advanced and can audit permission and schema changes, Idera’s Compliance manager is the most advanced and can even capture select statements.
Change Data Capture: This is built into SQL Server 2008+ but only in enterprise version.