title: tSQLt, triggers and testing
body: <p>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:</p>
  
  <p>First a little test database:</p>
  
  <pre><code>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 &gt; 10)
      RAISERROR('Too high number!', 16, 1);
  END;
  </code></pre>
  
  <p>And here is a tSQL test, to test the behaviour:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>CREATE TRIGGER triggerTestA
  ON testA FOR INSERT AS BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
      IF EXISTS (SELECT nr FROM Inserted WHERE nr &gt; 10)
        RAISERROR('Too high number!', 16, 1);
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION;
      THROW;
    END CATCH;
  END;
  </code></pre>
  
  <p>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.</p>
  
  <p>Could someone explain, and maybe help me do it right?</p>