In tSQLt, we can compare 2 tables using AssertEqualsTable command.
However, is there any way we can assert whether the values in the table have changed by a certain amount?
For example:
Table A: AccID|Balance
Table B: AccID|Balance
Is there any way in tSQLt to compare these two tables to check if the Balance have moved by a certain amount (say 100)? And fail if the change is more than +/-100?
The workaround is to use the usual SQL logic - that is - we can join both tables on AccID, then calculate the Balance difference and in the where clause enforce the criteria such that only those AccID's with a difference of +/-100 get displayed. Then allow test to succeed only if the output of the above returns 0 records.
tSQLt is a unit testing framework so the basic structure of a unit test should be:
Assemble which could include:
Not all tests will have an Assemble step
Act: Typically this will be a call to a stored procedure or function or SELECT from a view. If testing table constraints or triggers this step may involve inserting data into a table Most tests will have an Act step although this is not mandatory - for example the Assemble step may fake and populate some tables that drive the contents of a particular view and define the expected results then the Assert step could be used to compare the entire contents of the view with the expected result set.
Assert which could include:
Every test should have (preferably exactly) one assertion (although the code line for testing for exceptions will be located before the act).
So, considering the above and your requirements, that data can only change as the result of some action (in a procedure or trigger, or via direct DML statement) so your test must necessarily have an Act step. I cannot see how you would write a tSQLt test to assert that any data change is within your tolerance level regardless of the the process that changes it. When would you run that test? After which process or all processes? That is not a unit test it is an integration test. In fact arguably it is actually part of your application/program logic.
If you need to ensure that a change to a value never falls outside a tolerance level in your database (across two tables), you could try something like this:
1) Use a trigger that throws an exception if, on insert or update to Table A, the value in the Balance column on Table A is greater or less than the equivalent value in table B
2) Write the first test for that trigger which asserts that if a value in Table A is changed within your tolerance level then no error is thrown. That logic might look like this:
Assemble:
Fake TableA and TableB (tSQLt.Faketable)
Re-apply the trigger to the fake TableA (tSQLt.ApplyTrigger)
Add a row to both TableA and tableB that represents your start point
Assert:
Call tSQLt.ExpectNoException
Act: Update the row in TableA with a value that is within bounds
3) Write the next test for that trigger which asserts that if a value in Table A is changed outside of your tolerance level then an error IS thrown.
Assemble:
Fake TableA and TableB (tSQLt.Faketable)
Re-apply the trigger to the fake TableA (tSQLt.ApplyTrigger)
Add a row to both TableA and tableB that represents your start point
Assert:
Call tSQLt.ExpectException, optionally defining expected error number and/or message
Act: Update the row in TableA with a value that is outside of your tolerance level.
I would strongly recommend that you practice test-first development, so start by writing both tests, the first should pass and the second will fail. Then, when you create your trigger, both tests should pass and you can prove that the required logic in both positive and negative cases is valid.
Finally, the problem with this database-driven logic approach is that your application will then have to handle this error. This kind of logic is probably better placed in the application. The database is not after all, python so in this case LBYL (Look Before You Leap) is better than EAFP (Easier to Ask for Forgiveness that Permission)