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 :
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:
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; |