SQL: views and triggers
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.
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;