出现错误时退出并回滚脚本中的所有内容 [英] Exit and rollback everything in script on error

问题描述

我有一个 TSQL 脚本，它可以对数据库结构进行大量调整，但是在出现故障时让它继续运行并不真正安全.

I have a TSQL script that does a lot of database structure adjustments but it's not really safe to just let it go through when something fails.

把事情说清楚:

• 使用 MS SQL 2005
• 它不是一个存储过程，只是一个脚本文件 (.sql)

我拥有的是以下顺序的东西

what I have is something in the following order

BEGIN TRANSACTION
    ALTER Stuff
    GO

    CREATE New Stuff
    GO

    DROP Old Stuff
    GO
IF @@ERROR != 0
    BEGIN
  PRINT 'Errors Found ... Rolling back'
  ROLLBACK TRANSACTION
  RETURN
    END
ELSE
     PRINT 'No Errors ... Committing changes'
     COMMIT TRANSACTION

只是为了说明我正在使用的内容......无法详细说明现在，问题...

just to illustrate what I'm working with ... can't go into specifics now, the problem ...

当我引入错误(以测试事情是否回滚)时，我收到一条声明，指出 ROLLBACK TRANSACTION 找不到相应的 BEGIN TRANSACTION.这让我相信某些事情确实是错误的并且交易已经被终止了.我还注意到脚本没有完全退出错误，因此 DID 尝试在错误发生后执行每个语句.(当我没想到新表出现时，我注意到了这一点，因为它应该回滚)

When I introduce an error (to test if things get rolled back), I get a statement that the ROLLBACK TRANSACTION could not find a corresponding BEGIN TRANSACTION. This leads me to believe that something when REALLY wrong and the transaction was already killed. what I also noticed is that the script didn't fully quit on error and thus DID try to execute every statement after the error occured. (I noticed this when new tables showed up when I wasn't expecting them because it should have rollbacked)

推荐答案

发生错误时，事务自动回滚，中止当前批处理.

When the error occurs, the transaction is rolled back automatically, and the current batch is aborted.

然而，执行会继续到下一批.因此，执行错误后批处理中的所有内容.然后当您稍后检查错误时，您尝试回滚已回滚的事务.

Execution continues into the next batch, however. So all the stuff in the batches after the error gets executed. And then when you check for errors later, you try to rollback an already rolled back transaction.

此外，要停止整个脚本，而不仅仅是当前批处理，您应该使用:

Also, to stop the entire script, not just the current batch, you should use:

raiserror('Error description here', 20, -1) with log

见我的回答在这里 了解详情.

所以你需要在每批之后检查@error，我认为这样的事情应该可行:

So you need to check for @error after each batch, I think something like this should work:

BEGIN TRANSACTION
GO

ALTER Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

CREATE New Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

DROP Old Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

PRINT 'No Errors ... Committing changes'
COMMIT TRANSACTION

这篇关于出现错误时退出并回滚脚本中的所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！