Kolom aan tabel toevoegen en vervolgens bijwerken in transactie

Ik ben een script aan het maken dat op een MS SQL-server wordt uitgevoerd. Dit script voert meerdere instructies uit en moet transactioneel zijn. Als een van de instructies faalt, wordt de algehele uitvoering gestopt en worden eventuele wijzigingen teruggedraaid.

Ik heb problemen met het maken van dit transactiemodel wanneer ik ALTER TABLE-instructies geef om kolommen aan een tabel toe te voegen en vervolgens de nieuw toegevoegde kolom bijwerkt. Om meteen toegang te krijgen tot de nieuw toegevoegde kolom, gebruik ik een GO-opdracht om de ALTER TABLE-instructie uit te voeren en roep dan mijn UPDATE-instructie aan. Het probleem waarmee ik wordt geconfronteerd, is dat ik geen GO-opdracht kan geven in een IF-instructie. De IF-verklaring is belangrijk binnen mijn transactiemodel. Dit is een voorbeeldcode van het script dat ik probeer uit te voeren. Merk ook op dat het geven van een GO-commando de @errorCode-variabele verwijdert en in de code moet worden gedeclareerd voordat deze wordt gebruikt (dit staat niet in de onderstaande code).

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

Wat ik dus zou willen weten, is hoe ik dit probleem kan omzeilen, ALTER TABLE-statements afgeven om een ​​kolom toe te voegen en die kolom vervolgens bijwerken, allemaal binnen een script dat wordt uitgevoerd als een transactie-eenheid.

Antwoord 1, autoriteit 100%

GO is geen T-SQL-opdracht. Is een batchscheidingsteken. De clienttool (SSM, sqlcmd, osql enz.) gebruikt het om effectief het bestand te knippenbij elke GO en de afzonderlijke batches naar de server te sturen. Het is dus duidelijk dat je GO niet binnen IF kunt gebruiken, en je kunt ook niet verwachten dat variabelen het bereik van batches overspannen.

Je kunt ook geen uitzonderingen opvangen zonder te controleren op de XACT_STATE()om ervoor te zorgen dat de transactie niet gedoemd is.

Het gebruik van GUID’s voor ID’s is altijd op zijn minst verdacht.

Het gebruik van NOT NULL-beperkingen en het opgeven van een standaard ‘guid’ zoals '{00000000-0000-0000-0000-000000000000}'kan ook niet correct zijn.

Bijgewerkt:

• Verdeel de ALTER en UPDATE in twee batches.
• Gebruik sqlcmd-extensies om het script bij een fout te breken. Dit wordt ondersteund door SSMS wanneer sqlcmd-modus is ingeschakeld, sqlcmd, en is triviaal om het ook in clientbibliotheken te ondersteunen: dbutilsqlcmd.
• gebruik XACT_ABORTom een ​​fout te forceren om de batch te onderbreken. Dit wordt veel gebruikt in onderhoudsscripts (schemawijzigingen). Opgeslagen procedures en toepassingslogica-scripts gebruiken in plaats daarvan TRY-CATCH-blokken, maar met de juiste zorg: Afhandeling van uitzonderingen en geneste transacties.

voorbeeldscript:

: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

Alleen een succesvol script zal de COMMITbereiken. Elke fout zal het script afbreken en terugdraaien.

Ik heb COLUMNPROPERTYgebruikt om te controleren voor het bestaan ​​van kolommen kunt u in plaats daarvan elke gewenste methode gebruiken (bijv. lookup sys.columns).

Antwoord 2, autoriteit 75%

In tegenstelling tot de opmerkingen van Remus, kunt u de update uitvoeren in een 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 moesten dit doen bij het maken van upgradescripts. Meestal gebruiken we gewoon GO, maar het is nodig geweest om dingen voorwaardelijk te doen.

Antwoord 3, autoriteit 47%

Ik ben het bijna eens met Remus, maar je kunt dit doen met SET XACT_ABORT ON en XACT_STATE

In principe

• SET XACT_ABORT ON zal elke batch afbreken bij fout en ROLLBACK
• Elke batch wordt gescheiden door GO
• Uitvoering springt naar de volgende batch bij fout
• Gebruik XACT_STATE() om te testen of de transactie nog geldig is

Hulpprogramma’s zoals Red Gate SQL Compare gebruiken deze techniek

Zoiets als:

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

Ik heb ook de standaard verwijderd. Geen waarde = NULL voor GUID-waarden. Het is bedoeld om uniek te zijn: probeer niet elke rij op allemaal nullen te zetten, want het zal eindigen in tranen…

Antwoord 4, autoriteit 4%

Heb je het geprobeerd zonder de GO?

Normaal gesproken dient u tabelwijzigingen en gegevenswijzigingen niet door elkaar te gebruiken in hetzelfde script.

Antwoord 5, autoriteit 2%

Een ander alternatief, als u de code niet in afzonderlijke batches wilt splitsen, is om EXEC te gebruiken om een ​​geneste scope/batch te maken
zoals hier

Antwoord 6

Ik denk dat je een “;” kunt gebruiken om elk afzonderlijk commando te beëindigen en uit te voeren, in plaats van GO.

Houd er rekening mee dat GO geen deel uitmaakt van Transact-SQL:

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