如何在SQL Server上启用DDL事务处理

Question

如果我有以下脚本：

可能是副本:
是否可以在SQL Server内的事务中运行多个DDL语句？

如果我有以下脚本：

BEGIN TRAN
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_1]
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_2]
GO
COMMIT TRAN

事务不起作用。它仍然停留在事务一语句上。例如，如果语句1失败，则运行脚本时仍会执行语句2。

如何启用DDL的事务？

- KentZhou

Answer 1

您正在分批运行DDL，因此如果第一个语句引发的错误不足以终止连接（硬件问题等），则第二个批将运行。

管理工作室将GO视为批处理分隔符，并单独运行每个批处理。

您可以使用SET XACT_ABORT ON，在出现错误时自动回滚事务。您还可以删除GO语句，因为ALTER TABLE语句不需要在单独的批处理中运行。

- Code Magician

---

如果不移除GO，XACT_ABORT无法正常工作，因为它会在错误时回滚事务，然后执行所有显式事务外的其余批处理。 - Martin Smith

正确，他需要遵循这两个步骤才能获得他想要的行为。大多数 ALTER 错误不会批量终止，XACT_ABORT 将改变这种情况。 - Code Magician

Answer 2

MagicMike是正确的，但我实现了另一个我知道很有效的解决方案（即使他的解决方案看起来更优雅）。 值得一提的是，我的解决方案使用两个事务和清晰的错误处理（@@error在SQL Server上存在，请检查您的SQL上的等效项，在Oracle中应该是类似于“exception when others”而不是“If (@@error=0)”）。

begin tran

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_1]

IF (@@Error=0)
begin
    COMMIT TRAN
end
else
begin
    rollback tran
END

begin tran    
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_2]

IF (@@Error=0)
begin
    COMMIT TRAN
end
else
begin
    rollback tran
END

- Etienne

---

这个特定的例子将每个alter拆分成自己的事务。我相信你可以将后续的alter放在IF块中，以确保在那一点上没有发生错误，并将提交/回滚移动到像所示的最终块中。 - oglester

Answer 3

你不需要禁用或启用DDL命令

只需按照以下步骤进行操作 你就可以使用它

Begin Try
.......
End Try
Begin Catch
.......
End Try

以你的例子来说，你可以这样做

begin try

ALTER TABLE [dbo].temp CHECK CONSTRAINT [FK_1]
--GO

ALTER TABLE [dbo].temp CHECK CONSTRAINT [FK_2]
--GO

end try

begin catch
    print 'Error in the Try Block'
end catch

- Vikrant More