更新时间:2023-12-05 21:14:40
发生错误时,事务自动回滚,中止当前批处理.
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