Back to Notes

SQL Views, Procedures & Triggers

Server-side database objects that wrap queries or logic. Common interview territory even where production usage varies by team/philosophy.

Views

A stored SELECT — no data of its own, re-runs the underlying query on each access.

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = 'active' AND deleted_at IS NULL;

Why use one: hides multi-JOIN complexity behind one name, provides a security boundary (expose a column/row subset, revoke direct base-table access), and gives a canonical, single definition of a concept like "active customer" that every consumer shares instead of reimplementing.

Updatable views: only if simple — single base table, no aggregates, no DISTINCT/GROUP BY/UNION, includes the PK. Otherwise read-only.

Materialized views (PostgreSQL/Oracle) actually store the result, unlike a normal view — faster reads, but stale until explicitly refreshed:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT DATE(created_at) AS day, SUM(amount) FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;   -- non-blocking refresh

This is the same read-optimized-projection idea as [[Payment System]]'s materialized balance view — trading freshness for read speed, with an explicit refresh/reconciliation step.

Stored Procedures

CREATE PROCEDURE transfer_funds(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END;

Why use them: fewer network round trips (one CALL instead of several statements), can grant EXECUTE without granting direct table privileges (see [[SQL Security & Injection Defense]]), reusable across multiple client applications/languages.

Why teams increasingly avoid them: logic living outside the application codebase is hard to version, test, and code-review alongside the rest of the system; vendor lock-in (T-SQL, PL/SQL, PL/pgSQL aren't portable); application engineers can miss them entirely during a normal source review. Modern practice increasingly keeps business logic in application code, using the database purely for data — worth stating this tension explicitly rather than presenting stored procedures as an unqualified best practice.

Cursors — Usually an Anti-Pattern

DECLARE cur CURSOR FOR SELECT id FROM customers WHERE status = 'active';
OPEN cur;
-- FETCH in a loop, row by row...
CLOSE cur;

Row-by-row procedural iteration inside a procedure — almost always slower than an equivalent set-based UPDATE ... WHERE. Reach for a cursor only when the logic is genuinely procedural per row (e.g., calling an external system once per row), not as a default way to "process" data.

Triggers

CREATE TRIGGER audit_orders_insert
AFTER INSERT ON orders
FOR EACH ROW
INSERT INTO audit_log(table_name, action, row_id, ts) VALUES ('orders', 'INSERT', NEW.id, NOW());
  • Timing: BEFORE (can modify the row via NEW.column = ... before it's written), AFTER (row already written, can't modify it — used for audit/derived-table updates), INSTEAD OF (replaces the operation entirely, the mechanism that makes complex views updatable).
  • Pseudo-rows: NEW.col (post-change value), OLD.col (pre-change value, available on UPDATE/DELETE).
  • Common legitimate uses: audit logs, maintaining a denormalized counter (product.review_count), enforcing a business rule too complex for a plain CHECK.
  • Why they're risky: hidden side effects (an INSERT silently affecting 5 other tables is a real debugging nightmare), hard to test in isolation, can chain into trigger-on-trigger cascades, and add real overhead under write-heavy load. Use sparingly and document loudly — an application-level event handler is often clearer than a trigger for anything beyond simple auditing.

Interview Drills

  1. What's the practical difference between a normal view and a materialized view? — A normal view re-runs its underlying query on every access (always current, no storage cost beyond the query definition); a materialized view stores the computed result physically (fast reads, but stale until an explicit REFRESH) — the choice is a freshness-vs-read-speed trade-off, the same shape as caching.
  2. Why might a team deliberately avoid stored procedures even though they reduce network round trips? — Logic embedded in the database is invisible to normal application code review, hard to version-control alongside the rest of the codebase, and creates vendor lock-in (procedural SQL dialects aren't portable across databases) — teams increasingly prefer keeping business logic in application code and using the database purely for data storage.
  3. A trigger silently updates a review_count column whenever a review is inserted. What's the real risk of this pattern at scale? — The side effect is invisible at the call site — any engineer looking only at the INSERT INTO reviews statement in application code has no indication that a trigger elsewhere is also mutating a different table; this hidden coupling makes debugging and reasoning about write paths significantly harder as the trigger logic grows or chains with other triggers.

Cross-links

[[SQL Indexes & Query Performance]] · [[SQL Constraints & Schema DDL]] · [[SQL Transactions & ACID]] · [[SQL Security & Injection Defense]]