SQL ROLLBACK TRANSACTION

SQL ROLLBACK TRANSACTION is a SQL statement used to undo the changes made by a transaction and restore the database to its previous state.

---

1. Overview

SQL ROLLBACK TRANSACTION is used to cancel or undo the changes made by a transaction in a database. When you rollback a transaction, all the changes made by the transaction are discarded, and the database is reverted to its state before the transaction began.

Rolling back a transaction effectively cancels its effects and restores the database to its previous consistent state.

---

2. Syntax

The syntax for SQL ROLLBACK TRANSACTION is straightforward and consistent across most relational database management systems (RDBMS). Here's the syntax:

ROLLBACK TRANSACTION;

This statement rolls back the currently active transaction in the database, undoing its changes and restoring the database to its previous state.

---

3. Example

Consider a scenario where you have started a transaction and performed several database operations within it. Now, suppose you encounter an error or need to cancel the changes made by the transaction. You can use SQL ROLLBACK TRANSACTION to undo the transaction's effects:

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Perform database operations
INSERT INTO employees (name, salary) VALUES ('John Doe', 50000);
UPDATE departments SET manager_id = 1 WHERE department_id = 1;

-- Rollback the transaction
ROLLBACK TRANSACTION;

In this example, the SQL ROLLBACK TRANSACTION statement rolls back the changes made by the transaction, including the insertion of a new employee record and the update of a department's manager ID.

---

4. Conclusion

SQL ROLLBACK TRANSACTION is a crucial SQL statement used to undo the changes made by a transaction and restore the database to its previous state. By rolling back transactions, you can effectively cancel their effects and maintain database consistency.