如果有任何语句失败,将回滚的SQL查询
我想编写一个SQL脚本来执行多个单独的SQL语句;如果任何这些陈述失败,我想回滚整个交易。所以,像这样:如果有任何语句失败,将回滚的SQL查询
BEGIN TRANSACTION
insert into TestTable values (1)
insert into TestTable values (2)
insert into TestTabe values (3)
--if any of the statements fail
ROLLBACK
--else
COMMIT
这是用于MS SQL 2008.有什么我可以做到这一点?也许某种异常处理?
我意识到在我的例子中,我可以检查TestTable中的这些值,并确定这些语句是否以这种方式失败。但实际上,我的SQL将更加复杂,我宁愿将自己抽象为知道SQL在做什么。
自2005年以来的SQL Server已经有例外的支持:
BEGIN TRY
BEGIN TRAN
INSERT INTO ...
COMMIT TRAN
END TRY
BEGIN CATCH
EXECUTE usp_LogAndRethrowError
END CATCH
你LogAndRethrowError则可以回滚任何注定的交易,一拉:
-- Make sure we are not in a live or 'doomed' transaction
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
这是一个办法,我已经在这样做了过去:
Declare @HasError int;
set @HasError = 0;
BEGIN TRANSACTION
insert into TestTable values (1)
if (@@ERROR != 0)
set @HasError = 1
insert into TestTable values (2)
if (@@ERROR != 0)
set @HasError = 1
insert into TestTabe values (3)
if (@@ERROR != 0)
set @HasError = 1
if @HasError > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
我很懒,并且已将此行添加到我的所有陈述中
SET XACT_ABORT ON
http://technet.microsoft.com/en-us/library/ms188792.aspx
当SET XACT_ABORT为ON时,如果 的Transact-SQL语句产生 运行时错误,整个事务 终止并回滚。
当SET XACT_ABORT为OFF时,在某些情况下, 只引发错误的Transact-SQL语句 回滚 和交易继续 处理。根据错误的严重程度 ,当SET XACT_ABORT为OFF时,整个 事务可能会回退,即使在 也是如此。 OFF是默认设置 。
编译错误,如语法错误, 不受SET XACT_ABORT的影响。
XACT_ABORT必须对大多数 OLE DB提供程序,包括SQL服务器 一个隐含 或显式事务中设置用于数据 修改语句。唯一不需要此 选项的情况是 提供程序支持嵌套事务。 有关详细信息,请参阅分布式 查询和分布式事务。
SET XACT_ABORT的设置在执行或运行时设置为 ,而不是在 解析时间。
回滚在哪里? – Nathan 2010-02-10 17:29:08
@Nathan声明“您的LogAndRethrowError可以回滚任何注定事务”。所以你可以在Catch语句中添加ROLLBACK TRAN(或者让SP为你做)。 – Ben 2010-02-10 17:32:21
没有看到答案的某个部分。 – Nathan 2010-02-10 18:21:59