tSQLt, triggers and testing

Question

I have tried to wrap my brain around this, but can't make it work, so I present a little testcase here, and hopefully someone can explain it to me:

First a little test database:

CREATE DATABASE test;
USE test;
CREATE TABLE testA (nr INT)
GO

CREATE TRIGGER triggerTestA
ON testA
FOR INSERT AS BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT nr FROM Inserted WHERE nr > 10)
    RAISERROR('Too high number!', 16, 1);
END;

And here is a tSQL test, to test the behaviour:

ALTER PROCEDURE [mytests].[test1] AS
BEGIN
  EXEC tSQLt.FakeTable @TableName = N'testA'
  EXEC tSQLt.ApplyTrigger  
    @TableName = N'testA', 
    @TriggerName ='triggerTestA'
  EXEC tSQLt.ExpectException
  INSERT INTO dbo.testA VALUES (12)
END;

This test will run ok - but the trigger doesn't do what I want: prevent user from entering values > 10. This version of the trigger does what I want:

CREATE TRIGGER triggerTestA
ON testA FOR INSERT AS BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION;
  BEGIN TRY
    IF EXISTS (SELECT nr FROM Inserted WHERE nr > 10)
      RAISERROR('Too high number!', 16, 1);
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
  END CATCH;
END;

But now the test fails, stating A) there is an error (which was expected!) and B) that there is no BEGIN TRANSACTION to match a ROLLBACK TRANSACTION. I guess this last error is with the tSQLt surrounding transaction, and that my trigger somehow interferes with that, but it is sure not what I expect.

Could someone explain, and maybe help me do it right?

Answer 1

tSQLt is currently restricted to run tests in its own transaction and reacts, as you have seen, unwelcoming when you fiddle with its transaction.

So, to make this test work, you need to skip the rollback within the test but not outside.

I suggest this approach:

1. Remove all transaction handling statements from the trigger. You don't need to begin a transaction anyway as triggers are always executed inside of one.
2. If you find a violating row, call a procedure that does the rollback and the raiserror
3. Spy that procedure for your test

To test that procedure itself, you could use tSQLt.NewConnection