Problem testing history of temporal table using tsqlt framwork

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 26-Nov-20 10:21am

Meysam Toluie

Updated 26-Nov-20 10:23am

Add a Solution

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 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

[no name] 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)

Last 24hrs

This month

Pete O'Hanlon	200
Graeme_Grant	120
Ravi Bhavnani	90
Dave Kreskowiak	60
Andre Oosthuizen	60

Pete O'Hanlon	1,695
OriginalGriff	1,250
Graeme_Grant	810
Richard Deeming	743
Dave Kreskowiak	559

Problem testing history of temporal table using tsqlt framwork

Add your solution here

Preview 0

Problem testing history of temporal table using tsqlt framwork

Add your solution here

Preview 0

Existing Members

...or Join us