将列添加到表，然后在事务内更新它 [英] Add column to table and then update it inside transaction

问题描述

我正在创建一个将在MS SQL服务器中运行的脚本。该脚本将运行多个语句，并且需要进行事务处理，如果其中一个语句失败，则整体执行将停止，并且所有更改都将回滚。

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.

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

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

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

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.

推荐答案

GO不是T-SQL命令。是批量定界符。客户端工具（SSM，sqlcmd，osql等）使用它来有效地剪切每个GO上的文件，并将各个批次发送到服务器。因此，显然，您不能在IF内使用GO，也不能期望变量跨批处理。

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.

此外，如果不检查 XACT_STATE（） 以确保交易不会注定。

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

使用ID的GUID至少是可疑的。

Using GUIDs for IDs is always at least suspicious.

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

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

已更新：

• 将ALTER和UPDATE分为两批。


• 使用sqlcmd扩展名在出错时中断脚本。当sqlcmd模式打开时， SSMS对此，sqlcmd进行支持。也很容易在客户端库中支持它： dbutilsqlcmd 。


• 使用 XACT_ABORT 强制错误来中断批次。这在维护脚本（模式更改）中经常使用。通常，存储过程和应用程序逻辑脚本使用TRY-CATCH块代替，但要格外小心：异常处理和嵌套事务。

• 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.

示例脚本：

: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 。任何错误都会中止脚本并回滚。

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

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

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

这篇关于将列添加到表，然后在事务内更新它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！