在SQL Server中正确使用事务 [英] Correct use of transactions in SQL Server

问题描述

我有2条命令，需要两个命令都正确执行或都不执行。因此，我认为我需要一笔交易，但我不知道如何正确使用它。

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly.

以下脚本有什么问题？

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

INSERT 命令是执行，但是 UPDATE 命令有问题。

The INSERT command is executed, but the UPDATE command has a problem.

如果存在以下任何一种情况，我该如何实施以回退这两个命令

How can I implement this to rollback both commands if any of them have an error in execution?

推荐答案

添加try / catch块，如果事务成功，它将提交更改，如果事务失败，事务将回滚：

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

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  

这篇关于在SQL Server中正确使用事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！