What is the best way to track when table(s) are updated in SQL?
sql, sql-server
Solution
If you have a history table (A table with the same columns as the original table, plus an auto-increment ID column), you can track everything about changes to the original table. You can track inserts, deletes, and every change. Use triggers for insert, update, and deletes to put a row into the history table. If you don't need all these options, then use those that you do need.
If you choose to use an IsDeleted flag in the original table, it complicates every query, and leaves your active table with lots of unneeded rows. But that can work, depending on your needs.
Problem
In the past I have just added an field to each table and updated it with GETDATE() on every update/insert. The problem is now I have to keep track of delete too. I was thinking of just having a table that I would update when anything changed and add a trigger to all of the other tables. Ideas??? Thanks!