[Solved] Change Tracking Entity framework

by


You can track the operation, the changed columns and the new values by using Change Tracking. However getting the old Value out of Change Tracking is not possible. SQL Server 2016 offers the new feature “Change data capture”, which gives you the needed Information about the old value before the update/delete happened ( see https://msdn.microsoft.com/en-us/library/bb933994.aspx ).

If you don’t have access to a SQL Server 2016, here is how you can configure Change Tracking:

  • Activate at Database

    ALTER DATABASE <YourDatabase> f.e. DeviceDatabase
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

  • Activate Change Tracking for your needed tables

    ALTER TABLE <YourTable> f.e. Devices  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)

  • Setup a DB Job, which will copy change-information into your custom table every minute,hour,day (what you need)

    DECLARE @minversion bigint;
SET @minversion = (SELECT MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Devices')) )

SELECT SYS_CHANGE_COLUMNS, e.Id FROM
CHANGETABLE(CHANGES Devices, @minversion) AS C
LEFT OUTER JOIN Devices AS e  
ON e.Id = c.Id;

  • To Get the latest Value of the Changed Column you can try this (but beware of multiple updates of the same row. you only get the latest value).

    CHANGE_TRACKING_IS_COLUMN_IN_MASK
(COLUMNPROPERTY(OBJECT_ID('Devices'), 'Id', 'ColumnId')
,c.sys_change_columns)

This will return 1 if Column changed, 0 if not. You can add this for every column of your table and join on value = 1 and then add the value to your query.

Finally, I would just recommend to use Stored Procedures to Update/Insert/Delete on your Tables. In those you can easily insert all information you want to store about the change in your custom table.
If you have SQL Server 2016 tho, try what I mentioned above, eventually.

solved Change Tracking Entity framework