TSQL - Ajoute une colonne à la table puis la met à jour dans la transaction - GO

Question

Je suis entrain de créer un script qui sera exécuté dans un serveur MS SQL. Ce script va s'exécuter plusieurs instructions et doit être transactionnelle, si la déclaration d'échec de l'exécution globale est arrêté et toutes les modifications sont annulées.

J'ai de la difficulté de la création de ce modèle transactionnel lors de l'émission d'instructions ALTER TABLE pour ajouter des colonnes à une table, puis la mise à jour de la nouvelle colonne. Pour accéder à la nouvelle colonne à droite de suite, j'utilise un ALLER de commande pour exécuter l'instruction ALTER TABLE, et ensuite appeler mon instruction de mise à JOUR. Le problème, je suis confronté, c'est que je ne peut pas émettre une commande ALLER dans une instruction if. SI l'instruction est important dans mon modèle transactionnel. Ceci est un exemple de code du script que je suis en train de lancer. Notez également que la délivrance d'une commande ALLER, va jeter l' @errorCode variable, et devront être déclarés dans le code avant d'être utilisé (Ce n'est pas dans le code ci-dessous).

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

Donc ce que je voudrais savoir c'est comment faire pour contourner ce problème, l'émission d'instructions ALTER TABLE pour ajouter une colonne, puis la mise à jour de cette colonne, le tout dans un script d'exécution comme une unité transactionnelle.

Merci à l'avance!

Answer 1

ALLEZ n'est pas une commande T-SQL. Est un lot de délimiteur. L'outil client (SSM, sqlcmd, osql etc) utilise efficacement couper le fichier à chaque ALLER et de les envoyer au serveur les lots individuels. Alors, évidemment, vous ne pouvez pas utiliser d'ALLER à l'intérieur SI, et vous ne pouvez pas s'attendre à des variables de durée portée à plusieurs lots.

Aussi, vous ne pouvez pas intercepter des exceptions sans vérification de l' XACT_STATE() d'assurer que la transaction n'est pas vouée à l'échec.

Utilisation de Guid Id est toujours au moins suspecte.

En utilisant les contraintes not NULL et de fournir une valeur par défaut "guid" comme '{00000000-0000-0000-0000-000000000000}' aussi ne peut pas être correct.

il y a tout simplement trop d'erreurs ici, chaque ligne que vous écrivez est un problème. Je vous recommande de prendre un très débutant en T-SQL programmation livre et commencer la lecture. Vous n'êtes pas qualifié pour faire le travail que vous essayez de faire.

Mise à jour:

• Séparer les MODIFIER et de mettre à JOUR en deux lots.
• Utiliser sqlcmd extensions de briser le script en cas d'erreur. C'est pris en charge par SSMS lorsque le mode sqlcmd est sur, sqlcmd, et est trivial de le soutenir dans les bibliothèques clientes trop: dbutilsqlcmd.
• utiliser XACT_ABORT de la force d'erreur d'interrompre le traitement. Ceci est souvent utilisé dans les scripts de maintenance (modifications de schéma). Procédures stockées et de la logique de l'application les scripts en général l'utilisation de blocs TRY-CATCH au lieu de cela, mais avec des soins appropriés: la gestion des exceptions et des transactions imbriquées.

exemple de 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

Seulement un succès script va atteindre l' COMMIT. Toute erreur entraîne l'interruption du script et de restauration.

J'ai utilisé COLUMNPROPERTY pour vérifier la colonne d'existence, vous pouvez utiliser n'importe quelle méthode que vous aimez au lieu (par exemple. recherche sys.columns).

Answer 2

Je suis presque d'accord avec Remus, mais vous pouvez le faire avec SET XACT_ABORT et XACT_STATE

Fondamentalement

• SET XACT_ABORT on va annuler chaque lot d'erreurs et de RESTAURATION
• Chaque lot est séparé par ALLER
• L'exécution saute à la prochaine fournée sur erreur
• Utilisation XACT_STATE() permet de tester si la transaction est toujours valide

Des outils comme le Portail Rouge SQL de Comparer l'utilisation de cette technique

Quelque chose comme:

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

J'ai également supprimé le défaut. Aucune valeur = NULL pour les valeurs GUID. Il est destiné à être unique: n'essayez pas de les définir chaque ligne à tous les zéros, car il se termine dans les larmes...

Answer 3

Avez-vous essayé sans le GO?

Normalement, vous ne devez pas mélanger les modifications de table et les modifications de données dans le même script.

Answer 4

Vous pouvez placer UPDATE juste après ALTER TABLE. Si ALTER TABLE échoue, UPDATE ne sera pas exécuté.

 IF @errorCode = 0
BEGIN
 BEGIN TRY
  ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END
 

Answer 5

Je pense que vous pouvez utiliser un ";" pour terminer et exécuter chaque commande individuelle, plutôt que GO.

Notez que GO ne fait pas partie de Transact-SQL:

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