C. Transaction Control Language (TCL)

TCL commands are used to maintain consistency of our databases and for management of transaction made by DML commands. We can only use TCL commands with DML commands like INSERT, DELETE and UPDATE.

1. SAVEPOINT

The SAVEPOINT command allows us to pick a point in a transaction and save it so that we can roll back to it. This is similar to how you can backup your pictures to the cloud or you save a backup of an important project.

1SAVEPOINT SAVEPOINT_NAME;

2. COMMIT

By using the COMMIT statement we end the current transaction removing any existing savepoints that may be in use and make permanent all changes preformed in the transaction. Once the statement is run, we cannot roll back the transaction.

1DELETE FROM airbnb_listings
2WHERE id = 1001;
3COMMIT

3. ROLLBACK

To undo a transaction that are not saved to the database we use the ROLLBACK command. This can only be used to undo transactions that has came after the last COMMIT or ROLLBACK command that was ran. You can also rollback to a SAVEPOINT that has been created before.

1ROLLBACK TO SAVEPOINT_NAME;