sql · level 3 · unit 6 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A runs by itself whenever a table changes.
A shop must keep a record of every price change, and people forget. A trigger never forgets. It is a set of statements the database runs by itself whenever a chosen change happens to a table.
runs once a price has changed. Its statements go between BEGIN and END. Inside, is the price before and is the price after.
An insert has only NEW, a delete has only OLD, and an update has both.
CREATE TABLE price_changes (book_id INTEGER, old_price INTEGER, new_price INTEGER);
CREATE TRIGGER log_price AFTER UPDATE OF price ON books
BEGIN
INSERT INTO price_changes VALUES (OLD.id, OLD.price, NEW.price);
END;
UPDATE books SET price = 10 WHERE id = 1;
UPDATE books SET price = price - 1 WHERE id = 4;
SELECT * FROM price_changes ORDER BY book_id;This BEGIN opens the trigger's body. It does not start a transaction.
guess first
Two sales of Night Train. What is its stock now?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE TABLE sales (book_id INTEGER, copies INTEGER);
CREATE TRIGGER take_stock AFTER INSERT ON sales
BEGIN
UPDATE books SET stock = stock - NEW.copies WHERE id = NEW.book_id;
END;
INSERT INTO sales VALUES (4, 2), (4, 1);
SELECT title, stock FROM books WHERE id = 4;your turn
Your turn. Remember removed books.
Make a trigger that writes down the title of every book that gets deleted. Fill in the kind of change it reacts to, and which row holds the title.
result
Your answer appears here as a table.
fix it
The price log says nothing changed. Fix it!
result
Your answer appears here as a table.
- The log says the price went from 15 to 15. Which of the two values is wrong?
NEWis the row after the change. The price before the change lives somewhere else.- Change the middle NEW.price to OLD.price on line 5, then press run.
nailed it
the database keeps the records for you. :D
- A trigger runs by itself when a chosen change happens.
OLDis the row before the change,NEWthe row after.- An
AFTERtrigger reacts once the change is made.
new words in your collection
- trigger
- AFTER
- NEW
- OLD
This lesson isn’t saved. A free account remembers every lesson you finish.