Agregar columna a la tabla y luego actualizarla dentro de la transacción
Estoy creando un script que se ejecutará en un servidor MS SQL. Este script ejecutará varias sentencias y debe ser transaccional, si una de las sentencias falla, la ejecución general se detiene y los cambios se revertirán.
Tengo problemas para crear este modelo transaccional al emitir sentencias ALTER TABLE para agregar columnas a una tabla y luego actualizar la columna recién agregada. Para acceder a la columna recién agregada de inmediato, utilizo un comando GO para ejecutar el ALTER TABLE statement, y luego llame a mi declaración de ACTUALIZACIÓN. El problema que estoy enfrentando es que no puedo emitir un comando GO dentro de una declaración IF. La declaración IF es importante dentro de mi modelo transaccional. Este es un código de ejemplo del script que estoy tratando de ejecutar. Observe también que la emisión de un comando GO, descartará la @errorCode variable, y deben ser declarados en el código antes de ser usados (no está en el código de abajo).
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
Así que lo que me gustaría saber es cómo ir alrededor de este problema, emitir sentencias ALTER TABLE para agregar una columna y luego actualizar esa columna, todo dentro de un script que se ejecuta como una unidad transaccional.
6 answers
GO no es un comando T-SQL. Es un delimitador de lotes. La herramienta cliente (SSM, sqlcmd, osql, etc.) la usa para cortar el archivo en cada visita y enviar al servidor los lotes individuales. Así que, obviamente, no se puede utilizar IR dentro SI, ni se puede esperar variables para abarcar el ámbito a través de lotes.
Además, no puede capturar excepciones sin verificar la XACT_STATE()
para asegurar que la transacción no está condenada.
Usar GUID para IDs siempre es al menos sospechoso.
Usar restricciones NOT NULL y proporcionar un 'guid' predeterminado como '{00000000-0000-0000-0000-000000000000}'
tampoco puede ser correcto.
Actualizado:
- Separe el ALTER y la ACTUALIZACIÓN en dos lotes.
- Use extensiones sqlcmd para romper el script en caso de error. Esto es soportado por SSMS cuando el modo sqlcmd está en , sqlcmd, y es trivial soportarlo también en bibliotecas cliente: dbutilsqlcmd .
- uso
XACT_ABORT
para forzar a error a interrumpir el lote. Este se utiliza con frecuencia en scripts de mantenimiento (cambios de esquema). Los procedimientos almacenados y los scripts lógicos de la aplicación en general usan bloques TRY-CATCH en su lugar, pero con el cuidado adecuado: Manejo de excepciones y transacciones anidadas.
Script de ejemplo:
: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
Solo un script exitoso alcanzará el COMMIT
. Cualquier error abortará el script y revertirá.
He utilizado COLUMNPROPERTY
para comprobar la existencia de la columna, puede utilizar cualquier método que desee en su lugar (por ejemplo. búsqueda sys.columns
).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-21 18:45:43
Ortogonal a los comentarios de Remus, lo que puede hacer es ejecutar la actualización en un sp_executesql.
ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);
DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;
Hemos tenido que hacer esto al crear scripts de actualización. Por lo general, solo usamos GO, pero ha sido necesario hacer las cosas condicionalmente.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-05 15:20:58
Casi estoy de acuerdo con Remus pero puedes hacer esto con SET XACT_ABORT ON y XACT_STATE
Básicamente
- SET XACT_ABORT ON abortará cada lote en caso de error y REVERSIÓN
- Cada lote está separado por GO
- La ejecución salta al siguiente lote en caso de error
- Use XACT_STATE () comprobará si la transacción sigue siendo válida
Herramientas como Red Gate SQL Compare usan esta técnica
Algo como:
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
También he eliminado el valor predeterminado. No value = NULL para los valores GUID. Está destinado a ser único: no intentes establecer cada fila a todos los ceros porque terminará en lágrimas...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 20:19:09
¿Lo has probado sin el IR?
Normalmente no debe mezclar cambios de tabla y cambios de datos en el mismo script.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 19:32:05
Otra alternativa, si no desea dividir el código en lotes separados, es usar EXEC para crear un ámbito/lote anidado como aquí
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:15
Creo que puede usar un ";" para terminar y ejecutar cada comando individual, en lugar de IR.
Tenga en cuenta que GO no es parte de Transact-SQL:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 19:34:59