SQL: TCL Commands

What is TCL?

Transactional control commands(TCL) can only be used with DML commands like INSERT, UPDATE, and DELETE. They cannot be used when creating or deleting tables because the database automatically commits these operations.

NOTE: Before proceeding with TCL commands, you must know about the DML & DDL commands.

COMMIT command:

The Commit command is used to save any Transaction into the database permanently. Once you commit a transaction, it cannot be undone. Therefore, it is important to ensure that the changes made during the transaction are correct and have been properly validated before executing the COMMIT command.

Syntax:

COMMIT;

What do we mean by a Transaction?

A Transaction is simply any operation we perform on a Table.

Example:

Consider a products table:

ID	ITEM	PRICE
1	Basketball	899.00
2	Desk	1200.00
3	Chair	700.00

BEGIN TRANSACTION;
UPDATE products SET PRICE = 799.99 WHERE ID = 1;
UPDATE products SET PRICE = 1900.99 WHERE ID = 2;
COMMIT;

In this illustration, a new transaction is started by the BEGIN TRANSACTION command, and the products table is updated twice by the two UPDATE statements. These changes are permanently saved to the database by the COMMIT command.

Output:

ID	ITEM	PRICE
1	Basketball	799.99
2	Desk	1900.99
3	Chair	700.00

ROLLBACK Command:

In SQL, the ROLLBACK command is used to undo transactional changes and restore the database to its previous state. However, it can only be used before executing a COMMIT command. Once a transaction is committed, the changes made during the transaction are final and cannot be reversed.

Syntax:

ROLLBACK [To SAVEPOINT_NAME];

Example:

Considering the products table:

BEGIN TRANSACTION;
UPDATE products SET PRICE = PRICE - 100 WHERE ID = 1;
UPDATE products SET PRICE = PRICE + 100 WHERE ID = 2;
ROLLBACK;

In this illustration, the two UPDATE statements update the products table while the BEGIN TRANSACTION command initiates a new transaction. The ROLLBACK command reverses these modifications and returns the database to its original state.

SAVEPOINT Command:

A SAVEPOINT is a place in a transaction where a marker can be added to allow us to go back to a particular point in the transaction without going back completely. When there are multiple steps in a transaction, and we want to undo just a few of them without rolling back the entire transaction and starting over.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

BEGIN TRANSACTION;
UPDATE products SET ITEM = 'Volleyball' WHERE ID = 1;
SAVEPOINT update_items;
DELETE FROM products WHERE ID=2;
SAVEPOINT delete_item;
COMMIT;

The above example updates the products table as well as deletes the item where ID is 2, with savepoints update_items & delete_item. The following output will b generated:

ID	ITEM	PRICE
1	Volleyball	799.99
3	Chair	700.00

Now we will use the ROLLBACK command.

ROLLBACK TO update_items;

Output:

ID	ITEM	PRICE
1	Volleyball	799.99
2	Desk	1900.99
3	Chair	700.00

So this is how the SAVEPOINT could be used with the ROLLBACK command.

Note: also read about SQL: DELETE Query

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py