Topic 28 of 52 · Expert
Views, Procedures & Triggers
Reusable logic stored inside the database.
Concept
A view is a saved SELECT that behaves like a table — great for hiding complexity or restricting columns.
Stored procedures bundle SQL with parameters and control flow; functions return a single value and can be used in queries.
Triggers run automatically BEFORE/AFTER INSERT, UPDATE or DELETE — useful for audit logs, but hidden logic can surprise developers.
Code (SQL · MySQL 8)
CREATE VIEW v_customer_revenue AS
SELECT c.id, c.name, SUM(o.amount) AS revenue
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
DELIMITER //
CREATE PROCEDURE top_customers(IN n INT)
BEGIN
SELECT * FROM v_customer_revenue ORDER BY revenue DESC LIMIT n;
END //
DELIMITER ;
CALL top_customers(5);Remember
- Views store the query, not the data.
- Triggers add hidden side effects — document them.
- Use DELIMITER when creating procedures in the MySQL client.
Interview questions
View vs materialized view?
A view reruns its query each time. A materialized view stores the result and must be refreshed. MySQL has no native materialized views; use summary tables.
Procedure vs function?
A function returns one value and can be used in SELECT. A procedure is called with CALL, can return result sets and use OUT parameters.
Lesson test
Take an 8-question test on “Views, Procedures & Triggers” — concepts, output prediction, bug spotting and interview questions.