TSQL Try/Catch 在事务中，反之亦然? [英] TSQL Try / Catch within Transaction or vice versa?

问题描述

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

I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing.

这是脚本的摘要.

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 块放在交易中还是应该将交易放在 TRY 块中是正确的/最佳实践吗?

This is my first time writing transaction, is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?

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

The important factor in this script is that the user must manually commit the transaction.

推荐答案

只有在 TRY 块内和实际语句之前才打开事务，并立即提交.不要等待您的控件转到批处理的末尾来提交您的事务.

Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.

如果在 TRY 块中出现问题并且您打开了一个事务，控件将跳转到 CATCH 块.只需在那里回滚您的事务并根据需要进行其他错误处理.

If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required.

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

I have added a little check for any open transaction using @@TRANCOUNT function before actually rolling back the transaction. It doesn't really make much sense in this scenario. It is more useful when you are doing some validations checks in your TRY block before you open a transaction like checking param values and other stuff and raising error in the TRY block if any of the validation checks fail. In that case, the control will jump to the CATCH block without even opening a transaction. There you can check for any open transaction and rollback if there are any open ones. In your case, you really don't need to check for any open transaction as you will not enter the CATCH block unless something goes wrong inside your transaction.

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

Do not ask after you have executed the DELETE operation whether it needs to be committed or rolled back; do all these validation before opening the transaction. Once a transaction is opened, commit it straightaway and in case of any errors, do error handling (you are doing a good job by getting detailed info by using almost all of the error functions).

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

这篇关于TSQL Try/Catch 在事务中，反之亦然?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！