Chapter 3 · Performance and Production
Views, Stored Procedures, Functions and Triggers
- Page 10 of 22
- 19 min read
So far every query lived in your code: a Python string, a notebook cell, a report script. Databases can also store logic themselves. A view saves a query under a name, a materialized view saves its result, stored procedures and functions run code inside the server, triggers fire automatically when rows change, and events run on a schedule.
In data and AI work these show up constantly: a view that defines "a valid training example" once for every notebook, a materialized view that feeds a dashboard or a feature table, a trigger that records every change to a label so you can explain why a model's training set changed. They are also easy to overuse. This page shows each tool working on real databases, then gives you a rule for when logic belongs in the database and when it belongs in your application.
What you will learn
- Create and use views; which views can be updated, and
WITH CHECK OPTION - Materialized views in PostgreSQL and how to refresh them
- Stored procedures with IN/OUT parameters, variables and control flow (MySQL), and stored functions
- BEFORE and AFTER triggers, and a complete audit trail built with triggers (SQLite)
- Scheduled events, and when to keep logic in the database vs the application
Views: a saved query with a name
Revenue per order needs a join and a GROUP BY that every analyst would otherwise rewrite (and some would get wrong). Save it once:
CREATE VIEW order_totals AS
SELECT o.order_id,
o.customer_id,
o.order_date,
o.status,
SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;
SELECT c.name, COUNT(*) AS orders, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.status = 'delivered'
GROUP BY c.name
ORDER BY spent DESC
LIMIT 3;+---------------+--------+-------+
| name | orders | spent |
+---------------+--------+-------+
| Nadia Rahman | 3 | 23600 |
| Tanvir Ahmed | 2 | 19500 |
| Farhana Akter | 3 | 9500 |
+---------------+--------+-------+A view behaves like a table in SELECT, but it stores no data: every query against it runs the saved query again, so it is always current. Change an order and the view reflects it at once:
UPDATE order_items SET quantity = 3 WHERE order_id = 1 AND product_id = 4;
SELECT order_id, total FROM order_totals WHERE order_id = 1;+----------+-------+
| order_id | total |
+----------+-------+
| 1 | 3900 |
+----------+-------+Views are good for three things: one definition of a business rule ("revenue excludes cancelled orders"), simpler queries for analysts and BI tools, and access control — grant people a view without the sensitive columns instead of the table (see the next page). DROP VIEW order_totals; removes only the saved query, never data. A view on a view is allowed, but deep stacks get slow and hard to debug.
Can you write through a view?
In SQLite, never — views are read-only (unless you add INSTEAD OF triggers):
UPDATE order_totals SET total = 0 WHERE order_id = 1;Error: cannot modify order_totals because it is a viewPostgreSQL and MySQL can update a simple view — one table, no GROUP BY, DISTINCT, aggregates or UNION — by passing the change to the table underneath (MySQL can also update some join views, as long as each statement changes only one of the base tables). WITH CHECK OPTION stops a write through the view that would create a row the view itself cannot see:
-- PostgreSQL
CREATE VIEW dhaka_customers AS
SELECT customer_id, name, email, city
FROM customers
WHERE city = 'Dhaka'
WITH CHECK OPTION;
UPDATE dhaka_customers SET name = 'Nadia R. Rahman' WHERE customer_id = 1;
SELECT customer_id, name, city FROM customers WHERE customer_id = 1;
UPDATE dhaka_customers SET city = 'Sylhet' WHERE customer_id = 3;+-------------+-----------------+-------+
| customer_id | name | city |
+-------------+-----------------+-------+
| 1 | Nadia R. Rahman | Dhaka |
+-------------+-----------------+-------+
Error: new row violates check option for view "dhaka_customers"Renaming a customer worked and changed the real table. Moving a customer out of Dhaka was refused, because the row would vanish from the view. A view with an aggregate cannot be updated at all:
-- PostgreSQL
CREATE VIEW order_totals AS
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;
UPDATE order_totals SET total = 0 WHERE order_id = 1;Error: cannot update view "order_totals"Materialized views: a saved result
A normal view re-runs its query every time. When that query is expensive — a sales summary over millions of rows, a feature table aggregated per customer — and slightly stale data is acceptable, PostgreSQL can store the result as a materialized view, with indexes like a table:
-- PostgreSQL
CREATE MATERIALIZED VIEW product_sales AS
SELECT p.product_id,
p.name,
SUM(oi.quantity) AS units,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.product_id, p.name;
CREATE UNIQUE INDEX idx_product_sales_id ON product_sales (product_id);
SELECT * FROM product_sales ORDER BY revenue DESC LIMIT 3;+------------+-------------------------+-------+---------+
| product_id | name | units | revenue |
+------------+-------------------------+-------+---------+
| 7 | AI Engineering Bootcamp | 2 | 30000 |
| 5 | 27-inch Monitor | 1 | 18500 |
| 3 | Mechanical Keyboard | 2 | 8550 |
+------------+-------------------------+-------+---------+The price is staleness. A new order for two monitors does not appear until you refresh:
-- PostgreSQL
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (15, 4, '2026-06-20', 'delivered');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (15, 5, 2, 18500);
SELECT * FROM product_sales WHERE product_id = 5;
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales;
SELECT * FROM product_sales WHERE product_id = 5;+------------+-----------------+-------+---------+
| product_id | name | units | revenue |
+------------+-----------------+-------+---------+
| 5 | 27-inch Monitor | 1 | 18500 |
+------------+-----------------+-------+---------+
+------------+-----------------+-------+---------+
| product_id | name | units | revenue |
+------------+-----------------+-------+---------+
| 5 | 27-inch Monitor | 3 | 55500 |
+------------+-----------------+-------+---------+REFRESH MATERIALIZED VIEW recomputes the whole result. Without CONCURRENTLY it locks out readers while it runs; with it, readers keep seeing the old rows until the new ones are ready — but it needs a unique index, which is why we created one. Someone has to decide when to refresh: after each nightly load, on a schedule (see events below), or before a training run. MySQL and SQLite have no materialized views; you build the same thing as an ordinary table that a scheduled job empties and refills.
Stored procedures (MySQL)
A stored procedure is a named program kept in the database and run with CALL. It can take IN parameters, return values through OUT parameters, declare variables, branch and loop. Its body has several statements separated by ;, which confuses the mysql command-line client, so in that client you temporarily change the statement delimiter:
mysql shop <<'SQL'
DELIMITER //
CREATE PROCEDURE order_summary(IN p_order_id INT, OUT p_items INT, OUT p_total INT)
BEGIN
SELECT COUNT(*), COALESCE(SUM(quantity * unit_price), 0)
INTO p_items, p_total
FROM order_items
WHERE order_id = p_order_id;
END //
DELIMITER ;
SQLDELIMITER is a feature of that client, not of SQL. A driver such as PyMySQL sends each statement whole, so from Python you create the procedure directly. That is also how migration tools do it. The second procedure shows variables (DECLARE), branching (IF) and raising your own error (SIGNAL):
-- MySQL
SELECT product_id, name, stock FROM products WHERE product_id IN (6, 9) ORDER BY product_id;+------------+-----------------+-------+
| product_id | name | stock |
+------------+-----------------+-------+
| 6 | SQL Masterclass | NULL |
| 9 | USB-C Hub | 0 |
+------------+-----------------+-------+import os
import pymysql
mysql = pymysql.connect(
host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
database=os.environ["MYSQL_DATABASE"], autocommit=True,
)
ORDER_SUMMARY = """
CREATE PROCEDURE order_summary(IN p_order_id INT, OUT p_items INT, OUT p_total INT)
BEGIN
SELECT COUNT(*), COALESCE(SUM(quantity * unit_price), 0)
INTO p_items, p_total
FROM order_items
WHERE order_id = p_order_id;
END"""
RESTOCK = """
CREATE PROCEDURE restock(IN p_product_id INT, IN p_qty INT, OUT p_new_stock INT)
BEGIN
DECLARE v_stock INT;
IF p_qty <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'quantity must be positive';
END IF;
SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id;
IF v_stock IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'unknown product or stock not tracked';
END IF;
UPDATE products SET stock = stock + p_qty WHERE product_id = p_product_id;
SET p_new_stock = v_stock + p_qty;
END"""
with mysql.cursor() as cur:
for name, ddl in (("order_summary", ORDER_SUMMARY), ("restock", RESTOCK)):
cur.execute(f"DROP PROCEDURE IF EXISTS {name}")
cur.execute(ddl)
print("created", name)created order_summary
created restockNow call them. OUT parameters are written into session variables (names starting with @), which you read with a SELECT:
-- MySQL
CALL order_summary(6, @items, @total);
SELECT @items AS items, @total AS total;
CALL restock(9, 10, @new_stock);
SELECT @new_stock AS new_stock;
CALL restock(6, 5, @new_stock);+-------+-------+
| items | total |
+-------+-------+
| 1 | 15000 |
+-------+-------+
+-----------+
| new_stock |
+-----------+
| 10 |
+-----------+
Error: ERROR 1644: unknown product or stock not trackedThe USB-C hub (product 9) went from 0 to 10. The SQL course (product 6) has NULL stock, so the procedure refused with its own message and code 1644, the code MySQL uses for SIGNAL. A bad quantity is refused the same way:
-- MySQL
CALL restock(9, -5, @new_stock);Error: ERROR 1644: quantity must be positiveMySQL's procedural language also has WHILE … DO … END WHILE, REPEAT … UNTIL, LOOP with LEAVE, cursors and error handlers (DECLARE … HANDLER). PostgreSQL's equivalent is PL/pgSQL (CREATE PROCEDURE … LANGUAGE plpgsql, called with CALL, PostgreSQL 11+); SQL Server's is T-SQL. The syntax differs in every detail — the biggest portability cost of procedures.
Stored functions vs procedures
A function returns a value and can be used inside a query, like ROUND(). A one-statement MySQL function needs no BEGIN … END:
-- MySQL
CREATE FUNCTION order_total(p_order_id INT) RETURNS INT
READS SQL DATA
RETURN (SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items WHERE order_id = p_order_id);
SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1
ORDER BY order_id;+----------+-----------+-------+
| order_id | status | total |
+----------+-----------+-------+
| 1 | delivered | 2100 |
| 3 | delivered | 3000 |
| 10 | delivered | 18500 |
+----------+-----------+-------+READS SQL DATA is required when binary logging is on (the default in MySQL 8): every function must declare whether it is DETERMINISTIC, reads data, or uses no SQL, so replicas can replay it safely. The PostgreSQL version, written in plain SQL:
-- PostgreSQL
CREATE FUNCTION order_total(p_order_id int) RETURNS bigint
LANGUAGE sql STABLE
AS $$
SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items WHERE order_id = p_order_id
$$;
SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1
ORDER BY order_id;+----------+-----------+-------+
| order_id | status | total |
+----------+-----------+-------+
| 1 | delivered | 2100 |
| 3 | delivered | 3000 |
| 10 | delivered | 18500 |
+----------+-----------+-------+STABLE promises the function returns the same result for the same input within one query, which lets the planner optimise around it. In SQLite, you add functions from Python with con.create_function() (see Text, Number and Date Functions, CAST and COALESCE).
| Function | Procedure | |
|---|---|---|
| Called with | Inside a query: SELECT order_total(6) | CALL restock(9, 10, @s) |
| Returns | One value (or a table, in PostgreSQL) | Nothing, or values through OUT parameters (MySQL can also return result sets) |
| Changes data? | Can, but rarely should (MySQL forbids changing a table the calling statement uses) | Usually does |
| Transactions | Runs inside the caller's statement | Can COMMIT/ROLLBACK (MySQL; PostgreSQL 11+) |
| Typical use | Reusable calculation, a cleaning rule | A multi-step operation: place an order, nightly rebuild |
Triggers: code that runs when rows change
A trigger is attached to a table and fires automatically on INSERT, UPDATE or DELETE, once for each affected row (FOR EACH ROW; PostgreSQL also has FOR EACH STATEMENT triggers that fire once per statement). A BEFORE trigger runs before the change and can reject it (or, in MySQL and PostgreSQL, adjust the new values). An AFTER trigger runs once the change is made — the place for side effects such as logging. Inside, OLD is the row before and NEW the row after.
A BEFORE trigger as a guard
Say any price cut of more than half must go through an approval process. A guard in the database catches it from every app, script and manual edit:
CREATE TRIGGER trg_products_price_guard
BEFORE UPDATE OF price ON products
FOR EACH ROW
WHEN NEW.price < OLD.price * 0.5
BEGIN
SELECT RAISE(ABORT, 'price cut of more than 50% needs approval');
END;
UPDATE products SET price = 2000 WHERE product_id = 3;Error: price cut of more than 50% needs approvalThe same guard in MySQL uses SIGNAL inside BEGIN … END:
-- MySQL
CREATE TRIGGER trg_products_price_guard
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < OLD.price * 0.5 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'price cut of more than 50% needs approval';
END IF;
END;
UPDATE products SET price = 2000 WHERE product_id = 3;Error: ERROR 1644: price cut of more than 50% needs approvalIn PostgreSQL a trigger calls a separate trigger function written in PL/pgSQL (CREATE FUNCTION … RETURNS trigger, then CREATE TRIGGER … EXECUTE FUNCTION …), which can also raise an error or change NEW.
An audit trail with AFTER triggers
"Who changed this price, and what was it before?" An audit table plus three AFTER triggers answers that for every change, whichever program made it. json_object() stores the old and new values compactly:
CREATE TABLE product_audit (
audit_id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
action TEXT NOT NULL,
old_values TEXT,
new_values TEXT,
changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER trg_products_audit_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
INSERT INTO product_audit (product_id, action, new_values)
VALUES (NEW.product_id, 'insert', json_object('price', NEW.price, 'stock', NEW.stock));
END;
CREATE TRIGGER trg_products_audit_update
AFTER UPDATE ON products
FOR EACH ROW
WHEN OLD.price IS NOT NEW.price OR OLD.stock IS NOT NEW.stock
BEGIN
INSERT INTO product_audit (product_id, action, old_values, new_values)
VALUES (OLD.product_id, 'update',
json_object('price', OLD.price, 'stock', OLD.stock),
json_object('price', NEW.price, 'stock', NEW.stock));
END;
CREATE TRIGGER trg_products_audit_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
INSERT INTO product_audit (product_id, action, old_values)
VALUES (OLD.product_id, 'delete', json_object('price', OLD.price, 'stock', OLD.stock));
END;Now make some changes — including one that changes neither price nor stock:
UPDATE products SET price = 4200 WHERE product_id = 3;
UPDATE products SET stock = stock - 1 WHERE product_id IN (1, 4);
UPDATE products SET name = 'Python Crash Course (3rd ed.)' WHERE product_id = 1;
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (12, 'Webcam', 2, 3500, 20);
DELETE FROM products WHERE product_id = 11;
SELECT audit_id, product_id, action, old_values, new_values
FROM product_audit
ORDER BY audit_id;+----------+------------+--------+-----------------------------+---------------------------+
| audit_id | product_id | action | old_values | new_values |
+----------+------------+--------+-----------------------------+---------------------------+
| 1 | 3 | update | {"price":4500,"stock":12} | {"price":4200,"stock":12} |
| 2 | 1 | update | {"price":1200,"stock":40} | {"price":1200,"stock":39} |
| 3 | 4 | update | {"price":900,"stock":60} | {"price":900,"stock":59} |
| 4 | 12 | insert | NULL | {"price":3500,"stock":20} |
| 5 | 11 | delete | {"price":1000,"stock":null} | NULL |
+----------+------------+--------+-----------------------------+---------------------------+Five rows for five real changes. The rename did not touch price or stock, so the WHEN clause skipped it; the rejected price cut from earlier left no trace because the guard aborted it before anything happened. The changed_at column (not shown, since it holds the current time) records when. SQLite has no users, so the application must record who; in PostgreSQL and MySQL the trigger can store current_user / USER() — but that is the database account, and when every request arrives through one shared app account it says only "the app", so the application should still pass along the real person.
For ML this is valuable: if annotators change labels in a labels table, an audit trail lets you reconstruct exactly what the training data looked like on the day a model was trained.
Scheduled events
MySQL can run SQL on a timetable with events (the event scheduler is on by default in MySQL 8). Here, a nightly job rebuilds a small summary table — the "materialized view" MySQL does not have:
-- MySQL
CREATE TABLE daily_sales (
sales_day DATE PRIMARY KEY,
revenue INT NOT NULL
);
CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 DAY STARTS '2030-01-01 02:00:00'
DO
REPLACE INTO daily_sales (sales_day, revenue)
SELECT o.order_date, SUM(oi.quantity * oi.unit_price)
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_date;
SELECT EVENT_NAME, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();+---------------------+----------------+----------------+---------------------+---------+
| EVENT_NAME | INTERVAL_VALUE | INTERVAL_FIELD | STARTS | STATUS |
+---------------------+----------------+----------------+---------------------+---------+
| refresh_daily_sales | 1 | DAY | 2030-01-01 02:00:00 | ENABLED |
+---------------------+----------------+----------------+---------------------+---------+PostgreSQL has no built-in scheduler; the pg_cron extension adds one (SELECT cron.schedule('nightly-sales', '0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales');), and managed services such as Amazon RDS and Supabase offer it. SQLite has none. In practice many teams schedule outside the database — cron, Airflow, a cloud scheduler — because there the job is visible, logged, retried and alerted on (see ETL and ELT: Building a Reliable Data Pipeline).
Database or application?
| Put it in the database when… | Put it in the application when… |
|---|---|
| It must hold no matter who writes the data (constraints, guards, audit) | It is business logic that changes often and needs tests, code review and fast deploys |
| It saves moving large data out (aggregate millions of rows, return ten) | It calls other systems: APIs, an LLM, a message queue, email |
| Many tools read the same definition (a view for analysts and BI) | You need to switch databases, or run the same code on SQLite in tests |
| It is short, stable and set-based SQL | It is long procedural code that is easier to debug in Python |
| Feature | SQLite | MySQL 8 | PostgreSQL |
|---|---|---|---|
| Views | Yes (read-only) | Yes (simple ones updatable) | Yes (simple ones updatable) |
| Materialized views | No | No (use a table + event) | Yes |
| Stored procedures | No | Yes | Yes (11+) |
| Stored functions | From the host language (Python) | Yes | Yes (SQL, PL/pgSQL, others) |
| Triggers | Yes (body is SQL) | Yes | Yes (calls a trigger function) |
| Scheduled jobs | No | Events | pg_cron extension |
Common mistakes
- Expecting a view to be fast because it "exists". A view is a stored query, not stored data; it is exactly as fast as the query it hides. If it is slow, index the base tables or materialize it.
- Forgetting to refresh a materialized view. Dashboards and feature tables silently show yesterday's data. Refresh as part of the load job and record when it last ran.
- Hidden side effects. A trigger that updates other tables surprises the next developer and slows every write. Keep triggers small (guards, audit); document them; list them with
SELECT name FROM sqlite_schema WHERE type = 'trigger'. - Using
DELIMITERoutside the mysql client. PyMySQL or a migration tool sends it to the server, which rejects it as a syntax error (GUI editors differ: some understand it, some do not). Send theCREATE PROCEDUREstatement whole instead. - Re-running
CREATEscripts. A secondCREATE VIEWorCREATE PROCEDUREfails because the object exists. UseDROP … IF EXISTSfirst, orCREATE OR REPLACE VIEW(MySQL, PostgreSQL) andCREATE VIEW IF NOT EXISTS(SQLite).
Try it yourself
- Easy: Create a SQLite view
product_catalogshowing each product's name, its category name (or'Uncategorised') and price, and list the three most expensive products from it. - Medium: Add a SQLite BEFORE INSERT trigger on
order_itemsthat rejects a row whoseunit_priceis more than the product's current price, and show that it fires. - Hard: In MySQL, write a stored function
customer_spend(p_customer_id INT)that returns the customer's total spend on non-cancelled orders, and use it to list every customer with their spend.
Answers
-- 1. Easy
CREATE VIEW product_catalog AS
SELECT p.name AS product, COALESCE(c.name, 'Uncategorised') AS category, p.price
FROM products p
LEFT JOIN categories c ON c.category_id = p.category_id;
SELECT * FROM product_catalog ORDER BY price DESC, product LIMIT 3;-- 2. Medium
CREATE TRIGGER trg_order_items_price_check
BEFORE INSERT ON order_items
FOR EACH ROW
WHEN NEW.unit_price > (SELECT price FROM products WHERE product_id = NEW.product_id)
BEGIN
SELECT RAISE(ABORT, 'unit_price above the current list price');
END;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (13, 4, 1, 99999);Error: unit_price above the current list price-- MySQL
-- 3. Hard
CREATE FUNCTION customer_spend(p_customer_id INT) RETURNS INT
READS SQL DATA
RETURN (SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.customer_id = p_customer_id AND o.status <> 'cancelled');
SELECT customer_id, name, customer_spend(customer_id) AS spend
FROM customers
ORDER BY spend DESC, customer_id;Summary
- A view is a named query: always current, stores no data. Simple views are updatable in PostgreSQL and MySQL;
WITH CHECK OPTIONkeeps writes inside the view. - A materialized view (PostgreSQL) stores the result: fast to read, stale until
REFRESH MATERIALIZED VIEW(CONCURRENTLYneeds a unique index). - Procedures are called with
CALL, take IN/OUT parameters and can change data; functions return a value and are used inside queries.DELIMITERis only for the mysql client. - BEFORE triggers guard and adjust; AFTER triggers record — an audit trail captures every change no matter who made it.
- Keep database logic short and about data integrity; keep changing business logic and calls to other systems in the application.
Next: Security: Roles, SQL Injection, Sensitive Data and Backups controls who may run all this: users and roles with least privilege, read-only access through views, SQL injection and how parameters stop it, protecting personal data used for analytics and ML, and backups you can actually restore.