Intermediate20 min read

Views & Stored Procedures

Create views, stored procedures, triggers, and user-defined functions in MySQL.

Views

Views are saved SELECT queries that act like virtual tables. Use them to simplify complex joins for reporting users and to restrict column visibility for least-privilege access.

Views are not materialized by default—each query against a view runs the underlying SELECT. Heavy views on large tables need the same index support as raw queries.

CREATE VIEW active_customers AS
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.email;

Stored Procedures

Stored procedures encapsulate SQL logic on the server, reducing round trips for multi-step operations. They can accept parameters and return result sets or status codes.

Balance procedural SQL with application-layer logic—stored procedures can hide business rules and complicate testing and deployment unless your team standardizes on them.

DELIMITER //
CREATE PROCEDURE deactivate_user(IN p_user_id BIGINT)
BEGIN
  START TRANSACTION;
  UPDATE users SET status = 'inactive' WHERE id = p_user_id;
  UPDATE sessions SET revoked = 1 WHERE user_id = p_user_id;
  COMMIT;
END //
DELIMITER ;

Triggers

Triggers run automatically before or after INSERT, UPDATE, or DELETE on a table. Use them for audit logs, maintaining summary tables, or enforcing cross-row rules.

Triggers add hidden side effects—document them clearly and avoid chains that are hard to debug. Prefer application events or change data capture for complex workflows.

BEFORE triggers can modify NEW row values
Triggers fire per row—bulk updates invoke them many times
MySQL 8.0+ supports roles for granting EXECUTE on procedures

CREATE TRIGGER orders_audit AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
VALUES (OLD.id, OLD.status, NEW.status, NOW());

User-Defined Functions

UDFs return scalar values and can be used in SELECT expressions. They must be deterministic if marked for indexing or replication safety.

Stored functions differ from procedures in return semantics and usage context. Many teams implement reusable logic in application code instead for portability across databases.

CREATE FUNCTION discount_price(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN price * (1 - pct / 100);

When to Keep Logic in SQL

Keep data integrity constraints, aggregations close to data, and batch maintenance in SQL. Keep product rules, external integrations, and UI-specific formatting in application code.

Version control procedures and views in migration files. Test them in CI against a disposable database instance.