wake-up-neo.com

Fügen Sie der Tabelle eine Spalte hinzu und aktualisieren Sie sie dann innerhalb der Transaktion

Ich erstelle ein Skript, das in einem MS SQL Server ausgeführt wird. Dieses Skript führt mehrere Anweisungen aus und muss transaktional sein. Wenn eine der Anweisungen fehlschlägt, wird die Gesamtausführung gestoppt und alle Änderungen werden rückgängig gemacht.

Es treten Probleme beim Erstellen dieses Transaktionsmodells auf, wenn ALTER TABLE-Anweisungen ausgegeben werden, um einer Tabelle Spalten hinzuzufügen und dann die neu hinzugefügte Spalte zu aktualisieren. Um sofort auf die neu hinzugefügte Spalte zuzugreifen, verwende ich einen GO-Befehl, um die ALTER TABLE-Anweisung auszuführen, und rufe dann meine UPDATE-Anweisung auf. Das Problem besteht darin, dass ich keinen GO-Befehl in einer IF-Anweisung absetzen kann. Die IF-Anweisung ist in meinem Transaktionsmodell wichtig. Dies ist ein Beispielcode des Skripts, das ich ausführen möchte. Beachten Sie auch, dass die Ausgabe eines GO-Befehls die Variable @errorCode verwirft und vor der Verwendung im Code deklariert werden muss (Dies ist nicht im folgenden Code enthalten).

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

Was ich also wissen möchte, ist, wie man dieses Problem umgeht, ALTER TABLE-Anweisungen ausgibt, um eine Spalte hinzuzufügen, und diese Spalte dann innerhalb eines Skripts aktualisiert, das als Transaktionseinheit ausgeführt wird.

64
Guillermo Gomez

GO ist kein T-SQL-Befehl. Ist ein Stapelbegrenzer. Das Client-Tool (SSM, sqlcmd, osql usw.) verwendet es, um die Datei bei jedem Start effektiv zu schneiden und die einzelnen Stapel an den Server zu senden. Daher können Sie GO weder innerhalb von IF verwenden, noch können Sie erwarten, dass sich Variablen über mehrere Batches erstrecken.

Außerdem können Sie keine Ausnahmen abfangen, ohne nach XACT_STATE() zu suchen, um sicherzustellen, dass die Transaktion nicht zum Scheitern verurteilt ist.

Die Verwendung von GUIDs für IDs ist immer zumindest verdächtig.

Verwenden von NOT NULL-Einschränkungen und Bereitstellen einer Standard-Guid wie '{00000000-0000-0000-0000-000000000000}' kann auch nicht korrekt sein.

Aktualisiert:

  • Trennen Sie ALTER und UPDATE in zwei Gruppen.
  • Verwenden Sie sqlcmd-Erweiterungen, um das Skript bei Fehlern zu unterbrechen. Dies wird von SSMS, wenn der sqlcmd-Modus aktiviert ist , sqlcmd unterstützt und kann auch in Client-Bibliotheken problemlos unterstützt werden: dbutilsqlcmd .
  • verwenden XACT_ABORT um Fehler zu erzwingen, die Charge zu unterbrechen. Dies wird häufig in Wartungsskripten (Schemaänderungen) verwendet. Gespeicherte Prozeduren und Anwendungslogikskripten verwenden im Allgemeinen stattdessen TRY-CATCH-Blöcke, jedoch mit der richtigen Sorgfalt: Ausnahmebehandlung und verschachtelte Transaktionen .

beispielskript:

: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

Nur ein erfolgreiches Skript erreicht das COMMIT. Jeder Fehler bricht das Skript und das Rollback ab.

Ich habe COLUMNPROPERTY verwendet, um zu prüfen, ob Spalten vorhanden sind. Sie können stattdessen eine beliebige Methode verwenden (z. B. lookup sys.columns ).

42
Remus Rusanu

Orthogonal zu Remus 'Kommentaren können Sie das Update in einem sp_executesql ausführen.

ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);

DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;

Dies mussten wir beim Erstellen von Upgrade-Skripten tun. Normalerweise benutzen wir nur GO, aber es war notwendig, Dinge unter bestimmten Bedingungen zu tun.

21
Mark Sowul

Ich stimme Remus fast zu, aber Sie können dies mit SET XACT_ABORT ON und XACT_STATE tun

Grundsätzlich

  • SET XACT_ABORT ON bricht jeden fehlerhaften Stapel und jeden ROLLBACK ab
  • Jede Charge wird durch GO getrennt
  • Die Ausführung springt bei einem Fehler zur nächsten Charge
  • Mit XACT_STATE () wird geprüft, ob die Transaktion noch gültig ist

Tools wie Red Gate SQL Compare verwenden diese Technik

Etwas wie:

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

Ich habe auch die Standardeinstellung entfernt. Kein Wert = NULL für GUID Werte. Es soll eindeutig sein: Versuchen Sie nicht, jede Zeile auf alle Nullen zu setzen, da dies in Tränen enden wird ...

18
gbn

Hast du es ohne GO versucht?

Normalerweise sollten Sie Tabellenänderungen und Datenänderungen nicht im selben Skript mischen.

2
HLGEM

Wenn Sie den Code nicht in separate Stapel aufteilen möchten, können Sie auch EXEC verwenden, um einen verschachtelten Bereich/Stapel zu erstellen wie hier

1
M.Sabaa