Click here to Skip to main content
15,747,766 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello experts

For testing our store procedures in SQL we use tSQLt framework. There is a logic in our business that we have decided to use Temporal table by using histories of a row.
The problem is when a row of table updated 2 times in a Transaction, there are two records with same VersioningEndTime in the history table. As you know every test in tSQLt framework will be executed in a Transaction.

Is there any solution do solve this issue in tests?

What I have tried:

The algorithm in the test:
-- 1) Create a row
-- 2) WAITFOR DELAY '00:00:02'
-- 3) Update the row
-- 4) Select from History table

the result are 2 rows in the history table with same VersioningEndTime.
Updated 26-Nov-20 10:23am
CHill60 27-Nov-20 3:47am    
The problem with Transactions and Temporal tables is well known - discourse on it sql-server-temporal-table-tricks-and-troubles[^]
The obvious solution is don't update the row twice in the same transaction but as you haven't shared any code it is difficult for me to give you a specific solution
Meysam Toluie 27-Nov-20 6:14am    
I use no transaction. The algorithm I have wrote will executed in tSQLt test, and tSQLt will begin a transaction automatically and at the end it will be rollback.
CHill60 27-Nov-20 6:29am    
Then have the "1) Create a row, 2) Waitfor Delay '00:00:02'" in one test and "3) Update the row, 4) Select from History table" in another where the 2nd test is dependent on the first
Meysam Toluie 27-Nov-20 11:42am    
tSQLt does not work like this!
CHill60 27-Nov-20 11:59am    
You can have test dependencies in tSQLt for complex testing scenarios. At the end of the day, if you can't find a way for the two updates to be done outside of the same transaction then you have to live with the issue

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900