I need to create audit tables for tracking changes on insert/update/delete for each table of my database. (what fields have changed, username that made the change, modification date, action[insert/update/delete)
What is the best way of doing this?
Until now (in previous version of SQL Server) I was using a trigger in each table that was sending the "old" record to another table, but I'm not sure if that is the best way of tracking changes.
Thank you in advance.