• Depending on the number of changes, how often change happens, and how wide you're tables are, you'd have to implement different methods.

    The only change tracking I've had to implement in an OLTP system deals with settings so it was a fairly simple creation of a Settings_Log table that has a CreatedDateTime and DeletedDateTime. The Settings table has an ID, Name, Value. The Settings_Log table has ID, Name, Value, CreatedDateTime, DeletedDateTime. Whenever a record is updated or deleted, a trigger captures the deleted record and inserts it into the Settings_Log.

    I'd assume you'd do something similar to this in systems where there are small changes (small number of records changed) on narrow tables.

    I've also implemented change tracking in my DW for Type 1 dimensions. For the dimensions, I have a log table with an ID, Description, StartDateTime, EndDateTime, RowCount. The way I maintain history of the dimension is by creating a dimension_Log table with all the same fields and ChangeLogID. The dimension table would have ID, name, descr, etc..., LogID. The log table would have ID, name, descr, etc..., LogID, ChangeLogID. The change tracking logic is the same as with the settings (only updates and deletes). The method to capture this change is however very different. Since the amount of change is potentially a lot bigger I use the output clause during the UPSERT into the dimension and insert the output into the log table.

    This works pretty well, but there is quite a bit of storage wasted. If a dimension has 20 fields, when a single field is updated, the whole record gets logged. The storage waste isn't as much of an issue if the updates happen across most of the fields. Also, this assumes update to the table happens at a set interval versus per change that happens on the source table.

    One option I've read about but never implemented was CDC. I would think Change Data Capture would be something that you'd want to use in most other cases.