Utilisation correcte des transactions dans SQL Server

Question

J'ai deux commandes et j'ai besoin que les deux soient exécutées correctement ou qu'aucune ne soit exécutée. Je pense donc avoir besoin d'une transaction, mais je ne sais pas comment l'utiliser correctement.

Quel est le problème avec le script suivant ?

BEGIN TRANSACTION [Tran1]

INSERT INTO [Test].[dbo].[T1]
    ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]
  SET [Title] = N'az2' ,[AVG] = 1
  WHERE [dbo].[T1].[Title] = N'az'

COMMIT TRANSACTION [Tran1]
GO

Le site INSERT est exécutée, mais la commande UPDATE a un problème.

Comment puis-je implémenter ceci pour annuler les deux commandes si l'une d'entre elles a une erreur d'exécution ?

Answer 1

Ajoutez un bloc try/catch, si la transaction réussit, elle validera les changements, si la transaction échoue, elle sera annulée :

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH

Answer 2

Au début de la procédure stockée, il faut mettre METTRE XACT_ABORT ON pour demander à Sql Server d'annuler automatiquement la transaction en cas d'erreur. Si elle est omise ou réglée sur OFF, il faut tester les éléments suivants @@ERROR après chaque déclaration ou utilisation ESSAYER ... CATCH rollback bloc.

Answer 3

Approche facile :

CREATE TABLE T
(
    C [nvarchar](100) NOT NULL UNIQUE,
);

SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
BEGIN TRAN
    INSERT INTO T VALUES ('A');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('C');
COMMIT TRAN
SELECT * FROM T; -- Check after.
DELETE T;