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

发布于 2024-10-07 18:19:37 字数 1211 浏览 7 评论 0原文

我正在创建一个将在 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 语句来添加列，然后更新该列，所有这些都在作为事务单元执行的脚本中进行。

原文

I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back.

I am having trouble creating this transactional model when issuing ALTER TABLE statements to add columns to a table and then updating the newly added column. In order to access the newly added column right away, I use a GO command to execute the ALTER TABLE statement, and then call my UPDATE statement. The problem I am facing is that I cannot issue a GO command inside an IF statement. The IF statement is important within my transactional model. This is a sample code of the script I am trying to run. Also notice that issuing a GO command, will discard the @errorCode variable, and will need to be declared down in the code before being used (This is not in the code below).

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

So what I would like to know is how to go around this problem, issuing ALTER TABLE statements to add a column and then updating that column, all within a script executing as a transactional unit.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

娜些时光，永不杰束 2024-10-14 18:19:37

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

此外，如果不检查 XACT_STATE()确保交易不会失败。

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

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

Updated：

将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）。

GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cut the file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.

Also, you cannot catch exceptions without checking for the XACT_STATE() to ensure the transaction is not doomed.

Using GUIDs for IDs is always at least suspicious.

Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}' also cannot be correct.

Updated:

Separate the ALTER and UPDATE into two batches.
Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
use XACT_ABORT to force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.

example script:

: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

Only a successful script will reach the COMMIT. Any error will abort the script and rollback.

I used COLUMNPROPERTY to check for column existance, you could use any method you like instead (eg. lookup sys.columns).

回复收藏 0 原文

弃爱 2024-10-14 18:19:37

与 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，但有必要有条件地做事情。

Orthogonal to Remus's comments, what you can do is execute the update in an sp_executesql.

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

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

We've needed to do this when creating upgrade scripts. Usually we just use GO but it has been necessary to do things conditionally.

回复收藏 0 原文

蓝眼睛不忧郁 2024-10-14 18:19:37

我几乎同意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。它应该是独一无二的：不要尝试将每一行设置为全零，因为它会以泪水结束......

I almost agree with Remus but you can do this with SET XACT_ABORT ON and XACT_STATE

Basically

SET XACT_ABORT ON will abort each batch on error and ROLLBACK
Each batch is separated by GO
Execution jumps to the next batch on error
Use XACT_STATE() will test if the transaction is still valid

Tools like Red Gate SQL Compare use this technique

Something like:

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

I've also removed the default. No value = NULL for GUID values. It's meant to be unique: don't try and set every row to all zeros because it will end in tears...

回复收藏 0 原文