I have configured my SQL Server database with change tracking feature. Currently I can retrieve table records information for latest version only.
How can I query the tables to get the previous version of the records? Hence that I can compare what data has been modified.
Please find my current query that am using to fetch the information.
DECLARE @last_synchronization_version bigint;
SELECT
CT.product_id, p.CLM_STAT_CD, p.DLR_CODE, p.DLR_NAME,
p.comments, p.INVOICE_NO, p.INVOICE_LINE_NO, p.INVOICE_ITEM_SUFFIX,
tc.commit_time, ct.sys_change_version, c.updated_by,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT,
CT.SYS_CHANGE_VERSION as TrackingVersion
FROM
dbo.product AS p
RIGHT OUTER JOIN
CHANGETABLE(CHANGES dbo.product, @last_synchronization_version) AS CT
ON p.product_id = CT.product_id
JOIN
sys.dm_tran_commit_table tc ON CT.sys_change_version = tc.commit_ts
The above query is giving me the latest version changes information only. But I just want to check for the previous version changes also.
1条答案
按热度按时间2ul0zpep1#
AS Damien_The_Unbeliever and eshirvana have pointed out in the comments, Change Tracking does not support version tracking. Since Change Data Capture is not supported in Azure SQL, Temporal tables seem to be a viable alternative.