Correct use of transactions in SQL Server

Question

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.

What's the problem with the following 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, 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?

 

Answer 1

A transaction in SQL Server is a sequential group of statements or queries to perform single or multiple tasks in a database. Each transaction may have single read, write, update, or delete operations or a combination of all these operations.