Click here to Skip to main content
14,829,981 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:
SQL
-- 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.
Posted
Updated 26-Nov-20 10:23am
Comments
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 Tolouee 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 Tolouee 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
Gerry Schmitz 27-Nov-20 12:38pm
   
What's the big deal? You "know" under what circumstances you get "2" history records. You know the second is an "update". Who / what creates "VersioningEndTime"? What's the "resolution"?

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