Member-only story
SQL — Learn how to roll back queries in a transaction
Either execute all queries or none at all
Transactions are responsible for guaranteeing data validity despite errors and they are essential tools in your SQL-toolbox. All queries within a transaction either succeed or all fail; if the last one fails then the queries before get rolled back (gets undone). In this article you’ll learn how to use a transaction but first we’ll get into when to use a transaction:
0. When to use transactions
In some cases you need to perform two operations in your database that cannot be done at once (like with a MERGE INTO, UPDATE INTO, or DELETE INTO) but they still depend on each other. If either of them fails, none of them should succeed. Using transactions in SQL allow you to do just that; roll back previous transactions if one fails.
1. Setup
Lets illustrate this with an example. In our pizza company we have a PizzaMenu table and a PizzaPrices table. Each week we receive files for either table. Our goal is simple; we have to insert the weekly files into the table. The challenge is that we only to have pizza’s on the menu if there is a price in the database. Let’s create the tables:
2. Solving it the wrong way
Doing it the wrong way is to just insert data into both tables:
You’ll notice the error in the last record; we insert a string where only floats are allowed. If we execute the code above we’ll notice that the first insert succeeded and the second one failed. This is not what we want because we now have pizza’s on our menu that we don’t have prices for.
