ROLLBACK

In SQL, a ROLLBACK statement is used to undo a transaction that is currently in progress. When a transaction is started, SQL begins keeping track of all the changes made to the database during that transaction. If at any point during the transaction an error occurs or if the user decides to cancel the transaction, the ROLLBACK statement can be used to undo all the changes made up to that point and restore the database to its previous state.

Syntax

The syntax for a ROLLBACK statement in SQL is as follows:

ROLLBACK [ WORK | TRANSACTION [ transaction_name ] ]

WORK: This option is used to specify that a transaction is being rolled back.
TRANSACTION: This option is also used to specify that a transaction is being rolled back. You can optionally provide the name of the transaction that you want to roll back.
transaction_name: This is the name of the transaction that you want to roll back. It is only necessary to specify this if you used the TRANSACTION option.

Example

Here’s an example of how to use the ROLLBACK statement in SQL:

BEGIN TRANSACTION;

UPDATE Employees
SET Salary = 50000
WHERE EmployeeID = 123;

INSERT INTO EmployeeLog
VALUES (123, 'Salary updated to 50000', GETDATE());

-- An error occurs here, causing the transaction to be cancelled

ROLLBACK TRANSACTION;

-- The transaction is now cancelled and all changes have been undone
-- The Employees table and the EmployeeLog table are now back to their previous states

In the example above, we start a transaction and then update a salary field in the Employees table and add a log record in the EmployeeLog table. An error occurs after the update statement, causing the transaction to be cancelled. We then use the ROLLBACK statement to undo all the changes made during the transaction.