向表中添加列并在事务内更新它

Question

我正在创建一个将在MS SQL服务器上运行的脚本。该脚本将运行多个语句，并需要具有事务性，如果其中一个语句失败，则会停止整个执行并回滚任何更改。

当向表添加列并更新新添加的列时，我遇到了创建此事务模型的问题。为了立即访问新添加的列，我使用GO命令来执行ALTER TABLE语句，然后调用我的UPDATE语句。我面临的问题是，在IF语句内部无法使用GO命令。IF语句在我的事务性模型中很重要。以下是我尝试运行的脚本的示例代码。还请注意，发出GO命令将丢弃@errorCode变量，并且需要在使用之前在代码下面声明（以下代码中未显示）。

BEGIN TRANSACTION

DECLARE @errorCode INT
SET @errorCode = @@ERROR

-- **********************************
-- * Settings
-- **********************************
IF @errorCode = 0
BEGIN
 BEGIN TRY
  ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
  GO
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

IF @errorCode = 0
BEGIN
 BEGIN TRY
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

-- **********************************
-- * Check @errorCode to issue a COMMIT or a ROLLBACK
-- **********************************
IF @errorCode = 0
BEGIN
 COMMIT
 PRINT 'Success'
END
ELSE 
BEGIN
 ROLLBACK
 PRINT 'Failure'
END

所以我想知道如何解决这个问题，在一个作为事务单元执行的脚本中发出ALTER TABLE语句以添加列，然后更新该列。

- Guillermo Gomez

---

如果只执行DDL语句，您的数据库可能会处于模棱两可或无法维持的状态？难道您不能先执行DDL语句，然后再执行DML语句，在DDL语句失败时捕获任何DML语句中的错误吗？ - Tim

谢谢Tim，你说得很对！但愿我也能把你的评论选为解决方案。 - Guillermo Gomez

Answer 1

GO不是T-SQL命令，而是批处理分隔符。客户端工具（SSM、sqlcmd、osql等）使用它来在每个GO处有效地“切割”文件并将单独的批次发送到服务器。因此，显然您不能在IF内部使用GO，也不能期望变量跨批次范围。

此外，如果要确保事务未失败，则必须检查XACT_STATE()以捕获异常。

始终使用GUID作为ID至少是可疑的。

使用NOT NULL约束并提供默认值'guid'，例如'{00000000-0000-0000-0000-000000000000}'，也不可能是正确的。

已更新:

• 将ALTER和UPDATE分成两个批次。
• 使用sqlcmd扩展在错误时中断脚本。这受到SSMS在sqlcmd模式下支持、sqlcmd的支持，客户端库也很容易支持它: dbutilsqlcmd。
• 使用XACT_ABORT强制错误中断批处理。这经常用于维护脚本(模式更改)。存储过程和应用程序逻辑脚本通常使用TRY-CATCH块，但要注意：异常处理和嵌套事务。

示例脚本：

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

只有成功的脚本才能达到“COMMIT”。任何错误都会中止脚本并回滚。

我使用COLUMNPROPERTY来检查列是否存在，你可以使用任何你喜欢的方法（例如查找sys.columns）。

- Remus Rusanu

---

使用GUID作为ID至少是可疑的。为什么Asp.Net Identity表的PK列默认采用这种方式？ - caesay

这很棒 :) 但不要在PowerShell Invoke-Sqlcmd中使用它。如果没有错误，它将正常工作，但如果出现问题，不是所有内容都会回滚并执行其他批处理!!! - Yepeekai

Answer 2

与Remus的评论相反，您可以在sp_executesql中执行更新操作。

ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);

DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;

在创建升级脚本时，我们需要这样做。通常我们只使用GO，但有时需要有条件地执行操作。

- Mark Sowul

---

这正是我所需要的。谢谢你。 - Codure

非常感谢。救了我的一天。 - Asaad Mamoun

Answer 3

我几乎同意Remus的观点，但你可以使用SET XACT_ABORT ON和XACT_STATE来实现。

基本上：

• SET XACT_ABORT ON将在错误时中止每个批处理并回滚
• 每个批处理由GO分隔
• 出现错误时，执行跳转到下一个批处理
• 使用XACT_STATE()将测试事务是否仍然有效

像Red Gate SQL Compare这样的工具使用此技术。

类似以下内容：

SET XACT_ABORT ON
GO
BEGIN TRANSACTION
GO

IF COLUMNPROPERTY(OBJECT_ID('Color'), 'CodeID', ColumnId) IS NULL
   ALTER TABLE Color ADD CodeID [uniqueidentifier] NULL
GO

IF XACT_STATE() = 1
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
GO

IF XACT_STATE() = 1
 COMMIT TRAN
--else would be rolled back

我还去掉了默认值。对于GUID值，没有值 = NULL。它意味着是唯一的：不要尝试将每一行都设置为全零，因为这会以泪水告终...

- gbn

---

有趣的是，使用 XACT_STATE() 作为跨批处理保持状态的手段。没想到 Red Gate 也这样做。自动生成的代码可以通过在每个批处理开始时进行 IF XACT_STATE() 检查来防弹，但我不确定我会信任开发人员在整个脚本生命周期内保持这种纪律性...这就是为什么我更喜欢 :on abort exit，尽管它依赖于客户端工具的支持。 - Remus Rusanu

@Remus Rusanu：Red Gate仅使用SET XACT_ABORT ON和临时表来跨越GO。对于简单起见，使用XACT_STATE()更容易。您关于开发人员纪律的看法是正确的。我注意到您的更新与我的答案类似。 - gbn

Answer 4

你试过没有使用GO吗？

通常情况下，你不应该在同一脚本中混合表结构更改和数据更改。

- HLGEM

---

谢谢您的回复。我正在考虑将脚本拆分为两个部分，一个用于DDL，另一个用于DML。 - Guillermo Gomez

我最终将我的脚本分成了两个文件，一个是DDL，一个是DML。这样我就不需要在ALTER语句后加上GO了。 - Guillermo Gomez

@ ggomez：像 Red gate 工具一样使用 SET XACT_ABORT ON - gbn

谢谢 gbn，我会包含它！ - Guillermo Gomez

Answer 5

如果您不想将代码拆分为单独的批处理，则另一种选择是使用EXEC创建嵌套的作用域/批处理 如此。

- M.Sabaa