SQL: views and triggers

Before you start

SQL UPDATE and SELECT.

Name a reusable query as a view and record price changes automatically with a trigger.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

Code example

SQL

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;

Output

2|25|15
book|15
pen|5

Run code Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);

2CREATE TABLE price_log (product_id INTEGER, old_price INTEGER, new_price INTEGER);

4CREATE TRIGGER log_price AFTER UPDATE OF price ON product

5BEGIN

6 INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);

7END;

9CREATE VIEW cheap_products AS SELECT name, price FROM product WHERE price < 20;

11INSERT INTO product VALUES (1, 'pen', 5), (2, 'book', 25);

12UPDATE product SET price = 15 WHERE id = 2;

14SELECT * FROM price_log;

15SELECT * 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

Prepared trace of the published program. Some rows group repeated iterations.
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

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

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.