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.
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.
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.