Correct use of transactions in SQL Server

Question

I am having two commands and I want both of them executed correctly or none of them executed. Thus I guess I require a transaction, though I don't know how to use it correctly.

What is the issue with the below 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

The INSERT command is executed, however, the UPDATE command has a difficulty.

How to implement this to roll back both the commands if any of them is getting an error in the execution?

Answer 1

You can 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 

Looking for a SQL Tutorial? Join this SQL Course to gain more knowledge on SQL.