1. Handling mistakes
So far, we've been focusing on building transactions without worrying about errors. However, mistakes happen, and knowing how to handle those properly is a critical skill. We're going to start towards working towards mastering that ability. Let's start by learning how to use transactions to handle errors that happen when we are working interactively.
2. Mistakes happen
Let's take a look at an example with the cookies table we've been using in previous videos. When we are doing some manual updates in SQL, we might update a record by accident. For example, I want to update the quantity of Biscottis; however, I accidentally changed the Biscuits quantity from 1 to 13. I could write two update statements to reverse this, as shown here; however, there is a safer way to do this.
3. Rolling back mistakes
By using a transaction while I'm working, I can undo it by using the ROLLBACK command instead of commit. Let's repeat our mistake inside a transaction and test rollback. We begin our transaction and enter our errored statement. Now we enter rollback and select quantity for Biscuits. Notice that it didn't change at all.
4. Rollback multiple statements
We can also use rollbacks with multiple statements, just the same. For example, What if we want to update Cat's tongue cookies to deliciousness level 11 from 10 and Gingerbread Cookies to level 8 from 9. This time you might have noticed I made a mistake in the first statement by entering 111 instead of 11. Now, if I issue a rollback, what should we expect to see? Yeap, back to level 10 for Cat's tongue and level 9 for Gingerbread cookies. A rollback, by default, undoes the whole transaction.
5. Let's practice!
Let's go put this to use!