ChatGPT解决这个技术问题 Extra ChatGPT

将列添加到表中,然后在事务中更新它

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

在发出 ALTER TABLE 语句以将列添加到表然后更新新添加的列时,我无法创建此事务模型。为了立即访问新添加的列,我使用 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 语句来添加一列,然后更新该列,所有这些都在作为事务单元执行的脚本中。

如果只执行 DDL,您的数据库会处于模棱两可或无法维持的状态吗?如果 DDL 失败,您不能先执行 DDL,然后执行 DML,然后在 DML 上捕获任何错误?
谢谢蒂姆,你是对的!希望我也可以将您的评论作为解决方案投票。

G
George Stocker

GO 不是 T-SQL 命令。是批处理分隔符。客户端工具(SSM、sqlcmd、osql 等)使用它在每个 GO 处有效地剪切文件并将各个批次发送到服务器。所以很明显你不能在 IF 中使用 GO,你也不能期望变量跨越批次的范围。

此外,如果不检查 XACT_STATE() 以确保事务不会失败,您将无法捕获异常。

将 GUID 用于 ID 总是至少是可疑的。

使用 NOT NULL 约束并提供像 '{00000000-0000-0000-0000-000000000000}' 这样的默认“guid”也不正确。

更新:

将 ALTER 和 UPDATE 分成两批。

使用 sqlcmd 扩展在出错时中断脚本。这在 sqlcmd 模式打开时由 SSMS 支持,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)。


Using GUIDs for IDs is always at least suspicious. 是 Asp.Net Identity 表的 PK 列的默认值,为什么可疑?
这很棒 :) 但不要将它与 powershell invoke-sqlcmd 一起使用。如果没有错误,它将正常工作,但如果出现故障,并非所有内容都会回滚并执行更多批次!
M
Mark Sowul

与 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,但必须有条件地做事。


这正是我所需要的。谢谢你。
非常感谢。拯救了我的一天
g
gbn

我几乎同意 Remus,但您可以使用 SET XACT_ABORT ON 和 XACT_STATE 来做到这一点

基本上

SET XACT_ABORT ON 将在错误和 ROLLBACK 时中止每个批次

每批由 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。它是独一无二的:不要试图将每一行都设置为零,因为它会以眼泪结束......


有趣的是,使用 XACT_STATE() 作为跨批次保持状态的一种方式。不知道红门会这样做。自动生成的代码可以在每批开始时通过 IF XACT_STATE() 检查来防弹,但我不确定我是否相信开发人员会在整个脚本生命周期内保持这一原则......这就是我更喜欢 :on abort exit 的原因,尽管它依赖客户端工具来支持它。
@Remus Rusanu:Red Gate 仅使用 SET XACT_ABORT ON 和临时表来跨越 GO。为简单起见,使用 XACT_STATE() 更容易。不过,您对开发人员的纪律是正确的。我注意到您的更新也与我的回答相同
H
HLGEM

你试过没有 GO 吗?

通常,您不应该在同一个脚本中混合表更改和数据更改。


感谢您的答复。我正在考虑将脚本分成两部分。一种用于 DDL,一种用于 DML。
我最终将我的脚本分成两个文件,一个 DDL 和一个 DML。这样我就不需要 GO 在 ALTER 语句之后。
@ggomez:使用 SET XACT_ABORT ON 例如,Red Gate 工具
谢谢gbn,我包括在内!
C
Community

如果您不想将代码拆分为单独的批次,另一种选择是使用 EXEC 创建嵌套范围/批次 as here


d
davek

我认为您可以使用“;”终止并执行每个单独的命令,而不是 GO。

请注意,GO 不是 Transact-SQL 的一部分:

http://msdn.microsoft.com/en-us/library/ms188037.aspx


谢谢你这么快的回复。我已经尝试过了,但它不起作用。发出 UPDATE 语句会引发错误“列名 'ColorID' 无效”。
不,错误与批处理和编译有关。在解析时,CodeID 不存在。与语句终止符无关