Views, Procedures, Triggers

Server-side database objects that wrap queries or logic. Interview territory; production usage varies by team.

Views

Stored SELECT statement, behaves like a virtual table. No data of its own — re-runs underlying query each access.

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

SELECT * FROM active_customers WHERE id = 42;  -- queries the view

Why use views:

• Hide complexity (multi-JOIN logic behind one name)
• Security (expose subset of columns/rows; revoke base table access)
• Consistency (canonical definition of "active customer")
• Backwards-compat layer when refactoring schema

Updatable views: Can INSERT/UPDATE/DELETE through a view if simple — single base table, no aggregates, no DISTINCT/GROUP BY/UNION, includes PK. Otherwise read-only.

Materialized views: Stores result data (Postgres/Oracle), unlike normal views. Faster reads, but stale until refreshed.

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

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

MySQL doesn't have materialized views natively — emulate with table + scheduled job.

Drop:

DROP VIEW view_name;

Best practice: Use views for security boundaries and simplifying complex JOINs. Avoid deeply nested views (view-on-view-on-view) — query planner sometimes can't optimize across layers.

---

Stored Procedures

Named, server-stored block of SQL + procedural code. Called explicitly. Can have IN/OUT parameters.

-- MySQL
DELIMITER //
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 //
DELIMITER ;

CALL transfer_funds(1, 2, 100.00);

Why use procs:

• Network round-trip reduction (one CALL vs many statements)
• Encapsulate transaction logic
• DBA can grant EXECUTE without granting underlying table privileges
• Reuse from multiple apps/languages

Why avoid procs:

• Logic outside app codebase = hard to version, test, debug
• Vendor lock-in (T-SQL ≠ PL/SQL ≠ PL/pgSQL)
• App engineers can't see them in source review
• Modern apps mostly keep logic in code, DB for data only

Functions (UDF): Like procs but return a value, usable in SELECT.

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(101) DETERMINISTIC
RETURN CONCAT(first, ' ', last);

SELECT full_name(first_name, last_name) FROM users;

---

Cursors

Row-by-row iteration inside a procedure. Anti-pattern in most cases — set-based SQL is faster.

DECLARE done INT DEFAULT 0;
DECLARE cust_id INT;
DECLARE cur CURSOR FOR SELECT id FROM customers WHERE status = 'active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO cust_id;
    IF done THEN LEAVE read_loop; END IF;
    -- do something per row
END LOOP;
CLOSE cur;

Steps: DECLARE → OPEN → FETCH (loop) → CLOSE → DEALLOCATE (some DBMS).

Best practice: Avoid cursors. 99% of cursor logic = UPDATE ... WHERE with set-based filter. Cursors only when truly procedural (e.g., calling external system per row).

---

Triggers

Auto-execute on INSERT/UPDATE/DELETE (DML triggers) or CREATE/ALTER/DROP (DDL triggers, less common).

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());

Trigger timing:

• BEFORE — fires before row write; can modify the row via NEW.column = ...
• AFTER — fires after row write; can't modify row, used for audit/derived tables
• INSTEAD OF — replaces the operation (used on views, makes them updatable)

Trigger granularity:

• FOR EACH ROW — one fire per affected row (most common)
• FOR EACH STATEMENT — one fire per query (Postgres/Oracle)

Pseudo-rows:

• NEW.col — new value (INSERT, UPDATE)
• OLD.col — old value (UPDATE, DELETE)

Common uses:

• Audit logs
• Maintain denormalized counters (product.review_count)
• Auto-set updated_at (often handled by ON UPDATE CURRENT_TIMESTAMP instead)
• Enforce complex business rules CHECK can't express

Why avoid triggers:

• Hidden side effects — INSERT magically affects 5 other tables
• Hard to debug, hard to test
• Can cascade into trigger-on-trigger chains
• Bad for performance under load

Best practice: Use sparingly, document loudly. App-level event handlers usually clearer than triggers.

---

Optimization Concepts

Query optimizer picks an execution plan (index scan vs seq scan, hash join vs nested loop, etc.) using stats. Trigger via EXPLAIN / EXPLAIN ANALYZE.

See [[Indexes & Performance]] for plan reading and tuning.

---

Cross-refs

• [[Indexes & Performance]] — query plans, EXPLAIN
• [[DDL DML DCL TCL]] — CREATE/DROP for these objects
• [[Transactions]] — proc-level transactions
• [[SQL Security]] — GRANT EXECUTE on procs