ROLLBACK

Example

START TRANSACTION;
UPDATE Employees SET salary = salary + 100 WHERE employee_id = 1;
ROLLBACK;
SELECT * FROM Employees WHERE employee_id = 1;

Output

+-------------+-------+--------+
| employee_id | name  | salary |
+-------------+-------+--------+
|           1 | John  |   5000 |
+-------------+-------+--------+

Explanation

In the example, initially a transaction is started with the START TRANSACTION statement. An UPDATE statement is issued to increase the salary of an employee with the employee id of 1 by 100. However, the ROLLBACK statement is called immediately after which undoes the changes made by the UPDATE statement. Therefore, when the data is fetched, the salary of the employee with the id of 1 remains as it was prior to the UPDATE, hence displaying its original value.

Example

BEGIN;
INSERT INTO employees (name, position)
VALUES ('John Doe', 'Engineer');

SELECT * FROM employees;

ROLLBACK;

Output

• Example from SELECT * FROM employees before the ROLLBACK;

| id |     name    |  position  |
|----|:-----------:|:----------:|
|  1 |  John Doe   |  Engineer  |

• Example from SELECT * FROM employees after the ROLLBACK;

| id |     name    |  position  |
|----|:-----------:|:----------:|

Explanation

In the provided code example, first, a new transaction is begun with the BEGIN command. Then, an INSERT operation is performed to add a new employee record. A SELECT operation shows the table content after the insertion.

The ROLLBACK command is used to undo the operations performed within the current transaction. Therefore, the output after the ROLLBACK doesn’t include the recently inserted record which means John Doe has been removed from the list. Thus, it illustrates the SQL ROLLBACK operation’s ability to revert changes made within a transaction.

Example

BEGIN TRANSACTION;

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

ROLLBACK;

Output

No output will be generated as the transaction is rolled back.

Explanation

The ROLLBACK command in SQL is used to undo transactions that have not yet been saved to the database. The ROLLBACK command can also be used to undo a GROUP of transactions. In this example, the “INSERT INTO” transaction is cancelled using the ROLLBACK command. Thus, the new row is not inserted into the “Customers” table.

Example

DECLARE
BEGIN
    -- Setting a Savepoint
    SAVEPOINT save_pt;

    -- Inserting a record
    INSERT INTO employee (id, name) VALUES (1, 'John Doe');

    ROLLBACK TO save_pt;

    COMMIT;
END;

Output

No output is displayed for this transaction. The ROLLBACK statement undoes a transaction without providing a confirmation message.

Explanation

In this example, a savepoint named ‘save_pt’ is defined. A new record is inserted into the ‘employee’ table. Then, the ROLLBACK TO statement is used to undo the changes made after the savepoint ‘save_pt’. The COMMIT statement is used to end the transaction. The record inserted into the ‘employee’ table is not saved due to the ROLLBACK TO statement.

Example

BEGIN TRANSACTION;
  INSERT INTO employees (name, salary) VALUES ('John Doe', 50000);
  SELECT * FROM employees;
ROLLBACK;

Output

No rows found.

Explanation

The ROLLBACK command undoes all changes made during the current transaction. In the example, ‘John Doe’ was inserted into the ‘employees’ table. But because the ROLLBACK command was issued, that change was discarded and ‘John Doe’ was not in the ‘employees’ table when it was queried again.