Change data tracking with using triggers (not CDC)
Posted on 2014-01-09
I've to use triggers (insert, delete, update) for change data in SQL Server 2012.
The reason to use triggers as I've Standard edition only and CDC available only in Enterprise edition.
FYI, I am planning to make trigger only each table for DML opeation and trigger will make a log in replica of table (i.e. lets call history table for each) along with a flag which will denote either respective record is insert, update or delete.
Could you please assist on following points?
1. What will be the optimal way to fetch from history table as I need result at GUI with field name, old value, new value, modify by, modified date?
2. What are pros and cons for this approach as I have total around 180 tables?
FYI, the history table might have values which will be dependent on other tables like City Id in main table and for fetch City Name, we need to fetch from CityMaster table (For GUI).
Could you please assist on fetch change data with triggers only as CDC available only in Enterprise edition and I am working on Standard edition.