Dear colleagues,
Is there a way to apply change tracking changes in an order that respects FK relations? For instance, in our application, we have a table T_Vendor and a table T_VendorAddress, storing the vendor information and all vendor addresses. The T_VendorAddress table has an FK to T_Vendor. I include an example of an insert into T_Vendor, followed by an insert into T_VendorAddress in one transaction, and a delete of T_VendorAddress, followed by a delete of T_Vendor, also in one transaction. Both show the tables in alphabetical order. Is this a coincidence? Is there a way to retrieve the tables in order of the actual change of the record?
WITH [Changes] ([Version], [Table], [Type]) AS (
SELECT CT.SYS_CHANGE_VERSION AS [Version]
, 'T_VendorAddress' AS [Table]
, CT.SYS_CHANGE_OPERATION AS [Type]
FROM CHANGETABLE(CHANGES T_VendorAddress , NULL) CT
UNION
SELECT CT.SYS_CHANGE_VERSION AS [Version]
, 'T_Vendor' AS [Table]
, CT.SYS_CHANGE_OPERATION AS [Type]
FROM CHANGETABLE(CHANGES T_Vendor , NULL) CT
)
SELECT * FROM [Changes];
Result of the insert:
Result of the delete:

