Often we need track changes for the entire database, so we need design audit system to track changes. There are many best practices, such as separating the auditing table in another database, keep it small and fast, demoralize, etc. But at the end, we are facing how to design the auditing system.
There are a few approaches to design the auditing system:
- Build an auditing table for each base table;
- Build one generic auditing table to track changes of all database tables;
- Write changes to XML files which are stored outside of the database.
Triggers residing on database tables may capture changes and write to auditing table(s) or XML files. There are cons and pros for each one of them. None of them is perfect...
Read More