transaction - try catch sql server

Uso correcto de las transacciones en SQL Server 2008

Question

Tengo 2 comandos y necesito ambos ejecutados correctamente o ninguno de ellos se ejecuta. Entonces, creo que necesito una Transacción, pero no sé cómo usarla correctamente.

¿Cuál es el problema con el siguiente script?

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

El comando de insert ejecutó pero el comando de update tiene un problema. ¿Cómo puedo implementar esto para deshacer ambos comandos si alguno de ellos tiene un error en la ejecución?

Answer 1

Agregue un bloque try / catch; si la transacción tiene éxito, confirmará los cambios; si la transacción falla, la transacción se revierte:

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
GO

Answer 2

Al comienzo del procedimiento almacenado, se debe poner SET XACT_ABORT en ON para indicar al servidor SQL que replique automáticamente la transacción en caso de error. Si se omite o se establece en OFF, se necesita probar @@ERROR después de cada declaración o usar TRY ... CATCH rollback block.

Answer 3

Enfoque fácil:

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;