sql-server - TSQL 在事务中尝试/捕获，反之亦然？

Question

我正在编写一个脚本，该脚本将从多个表中删除记录，但在删除之前，它必须返回一个计数，以便用户在提交之前确认。

这是脚本的摘要。

BEGIN TRANSACTION SCHEDULEDELETE
    BEGIN TRY
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
    END TRY
    BEGIN CATCH 
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

            ROLLBACK TRANSACTION SCHEDULEDELETE
            PRINT 'Error detected, all changes reversed.'
    END CATCH

--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.

--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.

这是我第一次编写交易，在交易中包含 TRY/CATCH block 是正确/最佳实践还是交易应该在 TRY block 内？

此脚本中的重要因素是用户必须手动提交事务。

Answer 1

只有在进入 TRY block 且在实际语句之前才打开事务，并立即提交。不要等待您的控件到达批处理末尾才提交事务。

如果在 TRY block 中且已打开事务时出现问题，控件将跳转到 CATCH block 。只需在那里回滚您的事务并根据需要进行其他错误处理即可。

在实际回滚事务之前，我使用 @@TRANCOUNT 函数对任何打开的事务添加了一些检查。在这种情况下，这实际上没有多大意义。当您在打开事务之前在 TRY block 中进行一些验证检查(例如检查参数值和其他内容并在 TRY block 中引发错误(如果有))时，它会更有用的验证检查失败。在这种情况下，控件将跳转到 CATCH block ，甚至无需打开事务。在那里，您可以检查是否有任何未完成的事务，如果有任何未完成的事务，则可以回滚。就您而言，您确实不需要检查任何未完成的交易，因为除非交易内部出现问题，否则您不会进入 CATCH block 。

执行DELETE操作后不要询问是否需要提交或回滚；在开始交易之前进行所有这些验证。一旦打开一个事务，立即提交它，如果出现任何错误，请进行错误处理(通过使用几乎所有错误函数获取详细信息，您做得很好)。

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH