sql-server - tSQLt Private_RunTest 中也出现回滚错误

Question

每当我使用 ExpectException 时，我都会收到以下错误:(还有一个 ROLLBACK 错误 --> 当前事务无法提交且无法回滚到保存点。回滚整个事务。{Private_RunTest,140})

在 MSSQL 由于 RAISERROR 已经执行 ROLLBACK 之后，tSQLt 似乎正在尝试 ROLLBACK。我使用以下 IF 语句将以下 SELECT 和 SET 语句包装在 Private_RunTest 中，它似乎解决了问题。

IF ISNULL(@ExpectException,0) <> 1 
  BEGIN
    SELECT @Msg = COALESCE(@Msg, '') + ' (There was also a ROLLBACK ERROR --> ' + 
                  COALESCE(ERROR_MESSAGE(), '') + '{' + 
                  COALESCE(ERROR_PROCEDURE(), '') + ',' + 
                  COALESCE(CAST(ERROR_LINE() AS NVARCHAR), '') + '})';
    SET @Result = 'Error';
  END

这确实是一个错误和/或适当的修复吗？

Answer 1

您可能没有在 tsqlt 测试中使用 TRY/CATCH block 。

错误消息“还有一个 ROLLBACK ERROR --> 当前事务无法提交且无法回滚到保存点。回滚整个事务。”可以按如下方式重现:

1) 创建一个抛出错误并回滚的触发器，如下所示:

CREATE TRIGGER [dbo].[MyTable_IUDTR] ON [dbo].[MyTable]
    AFTER INSERT, UPDATE, DELETE
AS 
BEGIN

  BEGIN TRY
      RAISERROR('MyError', 16, 1)
  END TRY

  BEGIN CATCH
      THROW;
  END CATCH

END
GO

2)创建tsqlt测试来检查返回的错误消息:

CREATE PROC [ut_MyTable_IUDTR].[test that the error is returned]
AS
BEGIN

  DECLARE @ErrorMsg VARCHAR(50)

  EXEC tsqlt.FakeTable @TableName = 'MyTable'
  EXEC tsqlt.ApplyTrigger 'MyTable', 'MyTable_IUDTR'

  INSERT INTO dbo.MyTable
          ( FirstName, LastName )
  VALUES  ( N'John',N'Smith')

  SET @ErrorMsg = ERROR_MESSAGE()

  EXEC tSQLt.AssertEqualsString @Expected = 'MyError', @Actual = @ErrorMsg

END
GO

3)运行测试:

EXEC [tSQLt].Run 'ut_MyTable_IUDTR.test that the error is returned'

4) 您收到以下错误:

    There was also a ROLLBACK ERROR --> The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

修复:

更改 tsqlt 测试以包含 TRY/CATCH block ，如下所示:

ALTER PROC [ut_MyTable_IUDTR].[test that the error is returned]
AS
BEGIN

  DECLARE @ErrorMsg VARCHAR(50)

  EXEC tsqlt.FakeTable @TableName = 'MyTable'
  EXEC tsqlt.ApplyTrigger 'MyTable', 'MyTable_IUDTR'

  BEGIN TRY
      INSERT INTO dbo.MyTable
              ( FirstName, LastName )
      VALUES  ( N'John',N'Smith')
  END TRY

  BEGIN CATCH
      SET @ErrorMsg = ERROR_MESSAGE()
  END CATCH

  EXEC tSQLt.AssertEqualsString @Expected = 'MyError', @Actual = @ErrorMsg

END
GO