question

ChrisSijtsma avatar image
0 Votes"
ChrisSijtsma asked CarrinWu-MSFT commented

Change tracking: How do I apply changes in an order that respects FK constraints?

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:
94378-insert.png


Result of the delete:
94357-delete.png


sql-server-generalsql-server-transact-sql
insert.png (1.6 KiB)
delete.png (2.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ChrisSijtsma, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @ChrisSijtsma,

Welcome to Microsoft Q&A!

When I tested in my side, if I didn't commit in one transaction, the Version number will increase, so it will in an order:
94741-1.png

But if I commit in one transaction, the Version number will be the same.
94751-2.png
Maybe you could commit the transaction separately to get the result you want.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.png (32.5 KiB)
2.png (29.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

That is of course a coincidence. There is no ORDER BY clause, so SQL Server to shuffle the data in any way it prefers.

I don't do much Change Tracking, but shouldn't you sort on SYS_CHANGE_VERSION?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.