Добавить столбец в таблицу и затем обновить его внутри транзакции
Я создаю script, который будет запущен на сервере MS SQL. Этот script будет запускать несколько операторов и должен быть транзакционным, если один из операторов завершится неудачей, общее выполнение остановлено и все изменения будут отброшены назад.
У меня возникла проблема с созданием этой транзакционной модели при выпуске операторов ALTER TABLE для добавления столбцов в таблицу, а затем обновления вновь добавленного столбца. Чтобы сразу же получить доступ к вновь добавленному столбцу, я использую команду GO для выполнения инструкции ALTER TABLE, а затем вызывать оператор UPDATE. Проблема, с которой я столкнулся, заключается в том, что я не могу выпустить команду GO внутри оператора IF. Утверждение IF важно в моей транзакционной модели. Это пример кода script, который я пытаюсь запустить. Также обратите внимание, что выдача команды 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, чтобы добавить столбец, а затем обновить этот столбец, все в script, выполняемом как транзакционное устройство.
Ответы
Ответ 1
GO не является командой T-SQL. Является разделителем партии. Клиентский инструмент (SSM, sqlcmd, osql и т.д.) Использует его для эффективного вырезания файла при каждом GO и отправки на сервер отдельных партий. Таким образом, очевидно, что вы не можете использовать GO внутри IF, и вы не можете ожидать, что переменные будут охватывать область в пакетах.
Кроме того, вы не можете перехватывать исключения, не проверяя XACT_STATE()
, чтобы гарантировать, что транзакция не обречена.
Использование идентификаторов GUID для идентификаторов всегда подозрительно.
Использование ограничений NOT NULL и предоставление значения по умолчанию "guid" как '{00000000-0000-0000-0000-000000000000}'
также не может быть правильным.
Обновлено:
- Разделите ALTER и UPDATE на две партии.
- Используйте расширения sqlcmd для взлома script при ошибке. Это поддерживается SSMS, когда режим sqlcmd включен, sqlcmd, и тривиально также поддерживать его в клиентских библиотеках: dbutilsqlcmd.
- используйте
XACT_ABORT
, чтобы заставить ошибку прерывать пакет. Это часто используется в сценариях обслуживания (изменения схемы). Хранимые процедуры и сценарии логики приложения обычно используют блоки TRY-CATCH, но с надлежащим вниманием: Обработка исключений и вложенные транзакции.
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
Только успешный script достигнет COMMIT
. Любая ошибка отменяет script и откаты.
Я использовал COLUMNPROPERTY
, чтобы проверить наличие столбца, вы можете использовать любой метод, который вам нравится (например, lookup sys.columns
).
Ответ 2
Я почти согласен с 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
Я также удалил значение по умолчанию. Нет значения = NULL для значений GUID. Это должно быть уникальным: не пытайтесь установить каждую строку ко всем нулям, потому что она закончится в слезах...
Ответ 3
Комментарии ортогонального к 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, но нужно было делать что-то условно.
Ответ 4
Вы пробовали это без GO?
Обычно вы не должны смешивать изменения таблиц и изменения данных в том же script.
Ответ 5
Другая альтернатива, если вы не хотите разделить код на отдельные партии, заключается в использовании EXEC для создания вложенной области/партии
здесь
Ответ 6
Я думаю, вы можете использовать ";" для завершения и выполнения каждой отдельной команды, а не GO.
Обратите внимание, что GO не является частью Transact-SQL:
http://msdn.microsoft.com/en-us/library/ms188037.aspx