SQL ROLLBACK Statement

SQL Rollback Begin

This specifies the beginning of an explicit, local transaction. The BEGIN TRANSACTION statement initiates an explicit transaction, which ends with the COMMIT or ROLLBACK statement.

Example 1: PostgreSQL transactions via BEGIN, COMMIT, and ROLLBACK statements.

--begin the transaction
BEGIN;

-- deduct the amount from the account 1
UPDATE accounts 
SET balance = balance - 1500
WHERE id = 1;

-- add the amount from the account 3 (instead of 2)
UPDATE accounts
SET balance = balance + 1500
WHERE id = 3; 

-- roll back the transaction
ROLLBACK;

Example 2: begin transaction in sql:

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

Example 3: Begin Commit Transaction ROLLBACK in a Stored procedure SQL:

You can use dynamic parameters to create a stored procedure. How can we undo a transaction if it fails for whatever reason.

Create PROCEDURE  [dbo].[Save_PurchaseOrder]  
(
@UserId int,
@Vendorid int,
@CounterPerson varchar(50),
@ShippedTo varchar(20),
@ShippingCost  varchar(20),
@CustomerName  varchar(50),
@Location  varchar(50),
@OrderStatus char(1),
@ChangeStatusById int=0,
@ChangeStatusByName  varchar(50)='',
@MAXID nvarchar(200) output,
@OtherVendor nvarchar(50),
@OtherTechnician nvarchar(50)
) 
AS 

BEGIN
  
BEGIN TRANSACTION 
INSERT INTO [tblPurchaseOrder]
 ([UserId],[Vendorid],[PartsStatus]
 ,[ShippedTo],[ShippingCost],[CustomerName],[Location]
 ,[OrderStatus],[OrderDate],[ChangeStatusById],[ChangeStatusByName],OtherVendor,OtherTechnician)
  VALUES
 (@UserId ,@PartsStatus
 ,@ShippedTo,@ShippingCost,@CustomerName,@Location
 ,@OrderStatus,GETDATE(),@ChangeStatusById,@ChangeStatusByName,@OtherVendor,@OtherTechnician)
 
SELECT @MAXID=SCOPE_IDENTITY()
 IF @@ERROR<>0 
 BEGIN 
 ROLLBACK 
 SET @MAXID=0
 RETURN     
 END 
 COMMIT TRANSACTION 
END

SQL Rollback

The SQL ROLLBACK transaction statement returns an explicit or implicit transaction to its start point, or to a savepoint within the transaction. You can use ROLLBACK TRANSACTION to undo all data changes made since the transaction began or to a savepoint. It also releases transaction-held resources.

The ROLLBACK statement can be used to end a unit of recovery and roll back all of the relational database modifications made by that unit. ROLLBACK also stops the unit of work if relational databases are the sole recoverable resources used in the application process.

The rollback command is used to restore the table to its prior permanent status (undo state). If commit is used in the query, we will be unable to reverse the modifications made by the query.

ROLLBACK can also be used to undo only the modifications performed after a savepoint was set within the recovery unit without terminating the recovery unit. Select modifications can be undone by rolling back to a previous savepoint.

The BEGIN TRANSACTION keyword indicates where the transaction begins. It is used to cancel the entire transaction starting from the given Begin Transaction. Before running the delete command, use the ROLLBACK command to provide the original table that we stored.

NOTE: The ROLLBACK command is only useful if the transaction has not yet been committed.

Syntax:

ROLLBACK { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable  
     | savepoint_name | @savepoint_variable ]   
[ ; ]

Example 1: The effect of rolling back a named transaction is demonstrated in the following instance. The following statements create a table, initiate a specified transaction, insert two rows, and then roll back the transaction identified in the variable @TransactionName. Two rows are inserted by another statement outside of the named transaction. The results of the previous statements are returned by the query.

USE tempdb;  
GO  
CREATE TABLE ValueTable ([value] INT);  
GO  
  
DECLARE @TransactionName VARCHAR(20) = 'Transaction1';  
  
BEGIN TRAN @TransactionName  
       INSERT INTO ValueTable VALUES(1), (2);  
ROLLBACK TRAN @TransactionName;  
  
INSERT INTO ValueTable VALUES(3),(4);  
  
SELECT [value] FROM ValueTable;  
  
DROP TABLE ValueTable;

Output:

value
3
4

Example 2:

BEGIN TRAN T
SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

UPDATE dbo.EmpJobTitle SET JobTitle ='Sr. HR Manager'
WHERE EmpId =3;

SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

ROLLBACK TRAN T

SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

SQL Rollback After Commit

While we use Commit in a query, the changes that it makes are permanent and visible. We are unable to undo the Commit. The name of the transaction is tranName, and the command for the operation is the SQL statement that is used to perform the operation, such as changing or inserting data.

Rolls back an explicit or implicit transaction to its start point, or to a savepoint within the transaction.

You can use ROLLBACK TRANSACTION to undo all data changes made since the transaction began or to a savepoint. It also releases transaction-held resources.

A commit cannot be undone, rolled back, or reversed.


SQL Rollback After Delete

In the cases of Delete, Truncate, and Drop, we can rewind the data. When I perform queries, I can correctly rollback delete, drop, and truncate. Begin Transaction must be used before running the Delete, Drop, and Truncate queries.

While the database is in full recovery mode, it can use log files to rollback any changes made by DELETE. In complete recovery mode, TRUNCATE cannot be rolled back using log files.

If the current session is not closed, DELETE and TRUNCATE can both be rolled back while wrapped by TRANSACTION. TRUNCATE cannot be rolled back if it is written in Query Editor surrounded by TRANSACTION and the session is closed, however DELETE may.

Example 1: Create Database Ankit:

Create Table Tbl_Ankit(Name varchar(11))

insert into tbl_ankit(name) values('ankit');
insert into tbl_ankit(name) values('ankur');
insert into tbl_ankit(name) values('arti');

Select * From Tbl_Ankit

/*======================For Delete==================*/
Begin Transaction
Delete From Tbl_Ankit where Name='ankit'

Rollback
Select * From Tbl_Ankit

/*======================For Truncate==================*/
Begin Transaction
Truncate Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

/*======================For Drop==================*/
Begin Transaction
Drop Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

Example 2: Let us understand this concept in detail.

DELETE removes all entries from the table and records them in the Log file in case a rollback is required in the future. As a result, it moves slowly.

When TRUNCATE is used, SQL Server dealslocates the data files in the table and records the deallocation in the log files. Rollback can be used to recover deallocated data files that have been overwritten by other data. In the case of TRUNCATE, there is no guarantee of a rollback. However, using T-SQL, the following code shows that TRUNCATE can be rolled back for that specific session.

Create a test table with some data first. After that, execute the following T-SQL code in Query Editor to see how TRUNCATE affects the constructed test table.

BEGIN TRAN
TRUNCATE TABLE TestTable

-- Following SELECT will return TestTable empty
SELECT *
FROM TestTable

-- Following SELECT will return TestTable with original data
ROLLBACK
SELECT *
FROM TestTable

SQL Rollback After Update

You can revert the updated data while the transaction is open.

The rollback statement can be used to undo the transaction :

BEGIN TRANSACTION 
   --Update statement 
Rollback Transaction 

Example: The base table linked to a view can be updated. There are certain limitations to this, but they are outside the scope of this post for the beginning. We create a simple view of the EMPLOYEES table in the given description, then modify it.

CREATE OR REPLACE VIEW employees_v AS
SELECT * FROM employees;

UPDATE employees_v
SET    salary = 1000
WHERE  employee_id = 7369;

1 row updated.

ROLLBACK;

SQL Rollback After Table

With ROLLBACK, you can use many ALTER TABLE instructions in a single transaction. Certain statements are irreversible.

In particular, these statements contain data definition language (DDL) statements such as those that build or drop databases, tables, or stored procedures.

When I changed a table to add a column with the same name, I got an error and had to rollback (run the alter script twice on purpose to test the rollback).

I got an error saying that the column should distinct (since the column was already added during the first execution of the script).

Example 1: I want the sql execution to display the Failed instead and rollback the transaction. My code is as follows:

BEGIN TRANSACTION
 
ALTER TABLE dbo.tbl_name

ADD  [column1] [varchar] (20) NULL 
 
--Error handling
IF @@Error = 0  
  Begin
   COMMIT TRANSACTION
   print '-Success'   
END
ELSE
 Begin
  ROLLBACK TRANSACTION
  print '-Failed'
End
GO

Example 2:

BEGIN TRANSACTION
 BEGIN TRY
 ALTER TABLE1...
 ALTER TABLE2...
  
-- Additional data/structural changes
 COMMIT
 END TRY
 BEGIN CATCH
 ROLLBACK;
  THROW; -- Only if you want reraise an exception (to determine the reason of the exception)
END CATCH

SQL Rollback Create Table

Example 1: In our TestDB database, there are two tables with data. Now let's begin a transaction in the TestDB database and make some DDL modifications:

USE TestDB
GO

BEGIN TRANSACTION
 
TRUNCATE TABLE TableA

DROP TABLE TableB

CREATE TABLE TableC(ID INT) 

ROLLBACK

SELECT * FROM TableA
SELECT * FROM TableB
SELECT * FROM TableC

Example 2: creation of those tables survive the rollback of the transaction? How can I get them not to?

USE tempdb;
GO

CREATE DATABASE example;
GO

USE example;
GO

CREATE TABLE foo (a INT);
GO

INSERT INTO foo
VALUES (100);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
GO

ALTER TABLE foo ADD CHECK (a < 10);-- Gives error "The ALTER TABLE statement conflicted with the CHECK constraint…", 
as expected
GO

CREATE TABLE bar (b INT);
GO

ROLLBACK;-- Gives error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."  Huh?  
Where did our transaction go?
GO

SELECT *
FROM bar;-- Gives no error.  Table still exists!  NOT expected!
GO

USE tempdb;
GO

DROP DATABASE example;

SQL Rollback Drop Table

ROLLBACK reverses the updates made in the previous transaction block.

Roll back the current transaction, removing any modifications it would have caused.

Syntax:

ROLLBACK [ WORK | TRANSACTION ]

Example 1: Because the ROLLBACK command finishes the transaction, the season table is not removed:

premdb=# begin;
BEGIN
premdb=# drop table season;
DROP TABLE
premdb=# rollback;
ROLLBACK
premdb=# \d season

Table "public.season"

Column Type Modifiers
seasonid smallint
season_name character(9)
numteams smallint
winners character varying(30)

Example 2:

postgres=# begin;

BEGIN

postgres=# delete from test;

DELETE 1

postgres=# drop table test;

DROP TABLE

postgres=# rollback;

ROLLBACK

postgres=# 

Despite the DROP TABLE statement, the table "test" exists in the database and retains its data because the ROLLBACK statement was sent at the completion of the transaction block.


SQL Rollback if Insert Fails

If a trigger on an insert statement fails for some reason, roll back the insert statement.

Example 1: For example trigger on insert into employe(id,name) values(1,'ali')

CREATE TRIGGER [dbo].[Customer_INSERT]
  ON [dbo].[employe]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE @id INT
 
SELECT @id = INSERTED.id       
FROM INSERTED
 
INSERT INTO CustomerLogs
VALUES(@id, 'Inserted')
END

Example 2: I have several inserts on various tables, and I want them all to roll back if one fails.

DECLARE @TransactionName varchar(20) = 'Transaction1';
BEGIN TRY
BEGIN TRAN @TransactionName

INSERT INTO [dbo].Test1
(A --varchar
,B --Int
)
VALUES
('Test Fail' ,1)

DECLARE @ID bigint
SELECT @ID = SCOPE_IDENTITY()

INSERT INTO [dbo].Test2
(T1ID --Test1 ID
,A --varchar)
VALUES(@ID ,14)

COMMIT TRAN @TransactionName
END TRY
BEGIN CATCH
		
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
IF @@TRANCOUNT > 0
ROLLBACK TRAN @TransactionName

SELECT @ErrorMessage=ERROR_MESSAGE(),
@ErrorSeverity=ERROR_SEVERITY(),
@ErrorState=ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH ", );

close database connection(dbc);

SQL Rollback Multiple Transaction

Nested transactions are possible. By checking at @@TRANCOUNT, we can see how many layers of transactions we have. When we construct an explicit transaction, @@TRANCOUNT is increased by one. When we commit a transaction, @@TRANCOUNT is reduced by one. When we roll back a transaction, however, @@TRANCOUNT is set to 0. This means that no matter how deep we go in nested transactions, one ROLLBACK will undo everything and bring us back to the beginning.

Example 1: I'm having trouble understanding how to apply rollback when dealing with several transactions with distinct names. I'm using a try catch to force a rollback in the event of a mistake.

CREATE PROCEDURE InsertFromPDP
AS
BEGIN

DECLARE @tempTable TABLE
 (
  Id INT PRIMARY KEY,
  Referencia VARCHAR(15),
  UAP NVARCHAR(20),
  ConsumoInicialWeek01 FLOAT,
  ConsumoInicialWeek02 FLOAT,
  Stock INT,
  PecasPorCaixa INT,
  NumTurnos INT DEFAULT 3,
  NumPab INT DEFAULT 6,
  AlcanceAbastecimento INT DEFAULT 3,
  QtdMin INT DEFAULT 4,
  QtdMax INT DEFAULT 12,
  NumDias INT DEFAULT 5
  UNIQUE (Id)
 )

INSERT INTO 
@tempTable  
 (
  Id,
  Referencia,
  UAP,
  ConsumoInicialWeek01,
  ConsumoInicialWeek02,
  Stock,
  PecasPorCaixa
  )

SELECT * 
 FROM 
 viewConsumoPDP

 BEGIN TRY
  BEGIN TRAN InsertNotExistsReferenciasFromPDP;
INSERT INTO 
  Parametros
    
SELECT  M.Referencia, 
 M.UAP,      
 M.NumTurnos,
 M.NumPab,
 M.AlcanceAbastecimento,
 M.QtdMin,
 M.QtdMax,
 M.NumDias
 FROM 
 @tempTable M    
 WHERE 
 NOT EXISTS 
 (
 SELECT * 
 FROM Parametros P 
 WHERE 
 M.Referencia <> P.Referencia
 AND 
 M.UAP <> P.UAP 
 )

 BEGIN TRAN InsertConsumoFromPDP

 -- TODO--

 COMMIT InsertNotExistsReferenciasFromPDP
 COMMIT InsertConsumoFromPDP
 END TRY
 BEGIN CATCH
 IF @@TRANCOUNT > 0
 ROLLBACK InsertNotExistsReferenciasFromPDP
 ROLLBACK InsertConsumoFromPDP
 -- RAISE ERROR --
 END CATCH
END

Example 2:

-- Create a table to use during the tests
CREATE TABLE tb_TransactionTest (value int)
GO

-- Test using 2 transactions and a rollback on the 

-- outer transaction
BEGIN TRANSACTION
  PRINT @@TRANCOUNT
  INSERT INTO tb_TransactionTest VALUES (1)

  BEGIN TRANSACTION -- inner transaction
   PRINT @@TRANCOUNT
   INSERT INTO tb_TransactionTest VALUES (2)

  COMMIT -- commit the inner transaction
   PRINT @@TRANCOUNT
   INSERT INTO tb_TransactionTest VALUES (3)

ROLLBACK -- roll back the outer transaction
PRINT @@TRANCOUNT

SELECT * FROM tb_TransactionTest
GO

SQL Rollback Savepoint

A SAVEPOINT is a point in a transaction where you can revert to a previous state without reverting the entire transaction.

The rollback to savepoint command issues a java.sql.Connection.rollback request that has been overloaded to operate with a savepoint inside the current transaction and returns all work in the current transaction to the provided savepoint.

Syntax for a SAVEPOINT:

SAVEPOINT SAVEPOINT_NAME;

This command is only used to create a SAVEPOINT between all transactional statements. To undo a collection of transactions, use the ROLLBACK command.

The following is the syntax for rolling back to a SAVEPOINT.

ROLLBACK TO SAVEPOINT_NAME;

Example 1: The example below shows how to remove three separate records from the CUSTOMERS database. Before each delete, you should generate a SAVEPOINT so that you can ROLLBACK to any SAVEPOINT at any moment to restore the required data.

 

Consider the CUSTOMERS table having the following records.

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
SAVEPOINT SP1;
Savepoint created.

DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.

SAVEPOINT SP2;
Savepoint created.

DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.

SAVEPOINT SP3;
Savepoint created.

DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Now that the three removals have occurred, let us pretend you have changed your mind and have chosen to ROLLBACK to the SAVEPOINT you designated as SP2. The last two deletions are undone because SP2 was established after the initial deletion :−

ROLLBACK TO SP2;
Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2.

SELECT * FROM CUSTOMERS;
ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

Example 2: The values 102 and 103 that were input after the savepoint, my savepoint, was set are rolled back in the given description. At commit, just the values 101 and 104 are added.

INSERT INTO product_key VALUES (101);
SAVEPOINT my_savepoint;
INSERT INTO product_key VALUES (102);
INSERT INTO product_key VALUES (103);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO product_key VALUES (104);
COMMIT;

Example 3: roll back to the savepoint, and verify that the rollback worked:

splice> ROLLBACK TO SAVEPOINT savept1;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;

Output:

I
1
2

Example 4: The following example performs two INSERTs and an UPDATE; it includes two save points:

BEGIN
 INSERT INTO books (ISBN, CATEGORY,
 TITLE, NUM_PAGES,--  w w   w  . d  e    m  o  2s  .  c  o   m 
 PRICE, COPYRIGHT, AUTHOR1)
VALUES ('1234567893', 'Oracle Server',
 'Oracle Certification 071', 440, 35.99, 2015, 44);

SAVEPOINT A;

 INSERT INTO inventory (isbn, status, status_date, amount)
 VALUES ('1234567893', 'BACKORDERED', null, 1100);

SAVEPOINT B;

 UPDATE inventory
 SET status = 'IN STOCK'
 WHERE isbn = '1234567893';

ROLLBACK TO SAVEPOINT B;

COMMIT;
END;

We can see the impact of the ROLLBACK TO SAVEPOINT command with the following select:

SELECT b.title, i.status
FROM books b, inventory we
WHERE b.isbn = '1234567893'
AND b.isbn = i.isbn;

This returns the name of the book, as well as the status.

TITLE STATUS
Oracle Certification 071 BACKORDERED

SQL Rollback Transaction on Error

Example 1: rollback the transaction if an error has occurred in any SQL transaction:

BEGIN TRANSACTION NAME T1;
 
SELECT CURRENT_TRANSACTION();

delete from mytable;

insert into mytable values (456);
insert into mytable values ('Hello!');

ROLLBACK;

Example 2: The following example will perfectly explain you what the error happens :

START TRANSACTION;
 SET @c=0;

 INSERT INTO tbl_chart (acode,adesc)  VALUES (2,'3');
 INSERT INTO tbl_pics  (ID ,adesc) VALUES (1,'1');
 select ROW_COUNT() into @c ;

 IF ( c > 0) THEN
   COMMIT ;
 ELSE 
 ROLLBACK;
   END IF

and trying to check row_count and save its value in variable c . But this query gives error and it also saves the data after showing the error . where is the problem ?

And the error is this :

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ( c > 0) THEN COMMIT' at line 1 */

Example 3: query using Try/Catch block. First, I will delete all the records from the table.

TRUNCATE TABLE Test_tran  
BEGIN TRY  
 BEGIN TRAN  
 INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Amit')  
 INSERT INTO Test_Tran ( ID, Name) VALUES (2,'Kapil')  
 INSERT INTO Test_Tran ( ID, Name) VALUES (1,'Aditya')  
 COMMIT TRAN  
END TRY  
BEGIN CATCH  
ROLLBACK TRAN  
END CATCH 

We can see that no rows will be placed into the table after running the above query because it was rolled back when an error occurred, and we ensured atomicity by utilising the try/catch block.

Example 4: Rollback transaction on error:

/*SQL SERVER 2000 Error Handling*/
BEGIN TRANSACTION
 UPDATE MyChecking SET Amount = Amount - $990.00
 WHERE AccountNum = 12345
 IF @@ERROR != 0
 BEGIN
 ROLLBACK TRANSACTION
 RETURN
 END
 ELSE
 UPDATE MySavings SET Amount = Amount + $990.00
 WHERE AccountNum = 12345
 IF @@ERROR != 0
 BEGIN
 ROLLBACK TRANSACTION
 RETURN
 END
 ELSE
COMMIT TRANSACTION
GO

Output:

Msg 547, Level 16, State 1, Server JAVA2S\SQLEXPRESS, Line 4

The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking" the statement has been terminated.


SQL Rollback Transaction

In the current session, rolls back an open transaction.

In SQL, a rollback transaction can be used to go back to the start of a transaction or to a save point. You can use this SQL Rollback to fix problems or erase half-completed entries. Alternatively, it might be used at the conclusion of the transaction.

For example, if your transaction inserts a new record and then fails, you can use this rollback to restore the table to its previous state.

Example 1: Begin a transaction by inserting some data into a table, and then finish the transaction by rolling back the modifications:

select count(*) from a1;

Output:

COUNT(*)
0
begin name t4;
select current_transaction();

Output:

CURRENT_TRANSACTION()
1432071523422
insert into a1 values (1), (2);

Output:

number of rows inserted
2
rollback;
select count(*) from a1;

Output:

COUNT(*)
0
select current_transaction();

Output:

CURRENT_TRANSACTION()
[NULL]
select last_transaction();

Output:

LAST_TRANSACTION()
1432071523422

Example 2: In this instance, we'll add a new record to the Employee database and then perform a rollback transaction. We'll use Select Statement both within and outside of the transaction to accomplish this. Before the transaction is completed, the first select statement displays the table records.

BEGIN TRANSACTION

INSERT INTO [dbo].[EmployeeRecords] (
[EmpID], [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales])
VALUES (7, 'SQL Server', 'Tutorial', 'Masters', 'Learn', 55000, 1250)

SELECT * FROM [dbo].[EmployeeRecords]

ROLLBACK TRANSACTION

SELECT * FROM [dbo].[EmployeeRecords]

Though there is no error in the above statement, it has not inserted the record.

Example 3: Transaction code and you want to undo it, you can rollback your transaction:

BEGIN TRY
 BEGIN TRANSACTION
 INSERT INTO Users(ID, Name, Age)
 VALUES(1, 'Bob', 24)
        
DELETE FROM Users WHERE Name = 'Todd'
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH

SQL Rollback vs Commit

Main Article :- Sql difference between ROLLBACK and COMMIT Transaction

Rollback Commit
The COMMIT statement allows a user to save any modifications or modifications to the current transaction. These modifications are then permanent. The ROLLBACK statement allows a user to undo all changes and updates made to the current transaction since the last COMMIT.
The current transaction can not be undone once it has been fully processed with the COMMIT instruction. Once you've used the COMMIT command to (fully) execute the current transaction, it's impossible to undo and return it to its original state.
After the planned transaction has been completed successfully, the COMMIT statement is used. A rollback statement is issued when a transaction is terminated, there is a power outage, or the system executes incorrectly.
If all of the statements are completed properly and without errors, the COMMIT statement will store the current state. If any operations fail during the transaction's conclusion, it means that all of the modifications were not completed correctly, and we can undo them with the ROLLBACK statement.
When you use the commit command, the current transaction statement is saved and available to all users. The transaction state remains available to all viewers following the ROLLBACK command, but the current transaction may include incorrect data (it may also be right).
The syntax of COMMIT is: Commit; The syntax of ROLLBACK is: Rollback;