테이블에 열을 추가 한 다음 트랜잭션 내에서 업데이트합니다.

테이블에 열을 추가 한 다음 트랜잭션 내에서 업데이트합니다.

---

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 문을 실행하여 열을 추가 한 다음 해당 열을 업데이트하는 방법으로이 문제를 해결하는 방법입니다.

---

GO는 T-SQL 명령이 아닙니다. 일괄 구분 기호입니다. 클라이언트 도구 (SSM, sqlcmd, osql 등)는이를 사용 하여 각 GO에서 파일 을 효과적으로 잘라 내고 개별 배치를 서버로 보냅니다. 따라서 분명히 IF 내에서 GO를 사용할 수 없으며 변수가 배치에 걸쳐 범위를 확장 할 것으로 기대할 수 없습니다.

또한 XACT_STATE()트랜잭션이 실패하지 않았는지 확인하지 않고 예외를 포착 할 수 없습니다 .

ID에 GUID를 사용하는 것은 항상 의심 스럽습니다.

NOT NULL 제약 조건을 사용하고 기본 'guid'를 제공하는 '{00000000-0000-0000-0000-000000000000}'것도 올바르지 않습니다.

업데이트 :

• 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열 존재 여부를 확인하는 데 사용 했습니다. 대신 원하는 방법 (예 : lookup sys.columns)을 사용할 수 있습니다 .

---

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.

---

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

---

Have you tried it without the GO?

Normally you should not mix table changes and data changes in the same script.

---

Another alternative, if you don't want to split the code into separate batches, is to use EXEC to create a nested scope/batch as here

---

I think you can use a ";" to terminate and execute eachn individual command, rather than GO.

Note that GO is not part of Transact-SQL:

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

참고URL : https://stackoverflow.com/questions/4443262/add-column-to-table-and-then-update-it-inside-transaction