SQL: views and triggers
Name a reusable query as a view and record price changes automatically with a trigger.
CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
CREATE TABLE price_log (product_id INTEGER, old_price INTEGER, new_price INTEGER);
CREATE TRIGGER log_price AFTER UPDATE OF price ON product
BEGIN
INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);
END;
CREATE VIEW cheap_products AS SELECT name, price FROM product WHERE price < 20;
INSERT INTO product VALUES (1, 'pen', 5), (2, 'book', 25);
UPDATE product SET price = 15 WHERE id = 2;
SELECT * FROM price_log;
SELECT * FROM cheap_products ORDER BY name;2|25|15 book|15 pen|5
Explanation
A view is a stored query that behaves like a table. It does not copy data: each time the view is read, the query runs against the current tables. Views simplify repeated queries and can expose only some columns or rows to a user.
A trigger runs a block of SQL automatically when a table changes. This trigger fires after the price column of product is updated and writes the old and new prices to a log table. OLD and NEW are the row before and after the change. Triggers are useful for audit trails, but they make behaviour less visible, so use them sparingly.
Explore the dry run
CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);CREATE TABLE price_log (product_id INTEGER, old_price INTEGER, new_price INTEGER); CREATE TRIGGER log_price AFTER UPDATE OF price ON productBEGIN INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);END; CREATE VIEW cheap_products AS SELECT name, price FROM product WHERE price < 20; INSERT INTO product VALUES (1, 'pen', 5), (2, 'book', 25);UPDATE product SET price = 15 WHERE id = 2; SELECT * FROM price_log;SELECT * FROM cheap_products ORDER BY name;Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| The trigger runs after any UPDATE that changes the price column. | — | — | |
| OLD holds the row before the update and NEW the row after it. | — | — | |
| A saved query: the products that cost less than 20. | — | — | |
| The book's price changes from 25 to 15, which fires the trigger. | price_log gets one row | — | |
| The trigger recorded product 2 going from 25 to 15. | price_log gets one row | 2|25|15 | |
| The view reads the current prices, so the book now counts as cheap. | price_log gets one row | 2|25|15 book|15 pen|5 |
Cost and common mistakes
A view adds no storage. A trigger adds O(1) work per changed row.
- A view always reflects the current data. If you need a snapshot, copy the rows into a table.
- Triggers can fire other triggers and make bugs hard to find. Keep trigger logic small and documented.
- The inserts into product do not fire this trigger because it listens to updates of price only.
Try it yourself
What would price_log contain after a second update that sets the pen's price to 6?
Reveal answer and reasoning
A second row, 1|5|6, because the trigger fires for every update of the price column.