Views, stored procedures and triggers

Why it matters

Views give each user or service its own shape of the data and hide the rest, which is how data independence works in practice. Procedures and triggers let the database enforce rules no single application can be trusted to apply, and they also hide logic where application developers do not look, which is the source of many production surprises. DBMS exams ask about updatable views and trigger types; design reviews ask whether logic belongs in the database at all.

The idea

Views

A view is a named query that behaves like a table. Reading it runs the query (the database usually merges the view's definition into the outer query before optimising), so it always reflects current data and stores nothing.

Use	Example
Simplify repeated joins	order_summary joining orders, customers and totals
Security and least privilege	A view exposing employees without salary, granted to a reporting role
Logical data independence	Keep an old table's shape as a view after splitting the table
Stable contract for consumers	Analytics reads views, so internal tables can change

Updatable views. Inserts and updates through a view work when each view row maps to exactly one base-table row: one table, no aggregates, no DISTINCT, no GROUP BY, no set operations, and the key columns present. Views over joins or aggregates are read-only unless you define INSTEAD OF triggers that say how to apply the change. WITH CHECK OPTION rejects changes through a view that would produce rows the view itself would not show (inserting a 'Sales' employee through a view of 'Research' staff).

Materialized views store the query result and refresh it, trading freshness for speed; the System Design track's materialized views lesson covers refresh strategies.

Stored procedures and functions

A stored procedure is code (in PL/pgSQL, T-SQL, PL/SQL and similar) stored and executed inside the database; a function returns a value and can be used in queries. They run next to the data, so a multi-step operation needs one round trip instead of many, and permission can be granted to call the procedure without granting access to the tables it touches.

Triggers

A trigger runs automatically when an event happens on a table.

Dimension	Options	Notes
Event	INSERT, UPDATE (optionally of specific columns), DELETE, sometimes DDL	What fires it
Timing	BEFORE, AFTER, INSTEAD OF (on views)	BEFORE can change or reject the new row; AFTER sees the final state
Granularity	FOR EACH ROW or FOR EACH STATEMENT	Row triggers see OLD and NEW values
Condition	WHEN clause	Fires only when a condition holds

Good uses: audit logs (record OLD and NEW values on every change), maintaining a derived column or summary table transactionally, enforcing a cross-row rule a CHECK cannot express ("at most three active loans per member"), and keeping updated_at current. Bad uses: calling external services, long computations, and chains of triggers firing triggers, which make writes slow and behaviour hard to trace.

Trade-offs

Logic in the database is enforced for every writer (scripts, other services, manual fixes), runs next to the data and can be granted narrowly; it is also harder to version, test, debug and observe than application code, couples you to one database's dialect, and is invisible to developers reading the application. A common balance: constraints and simple triggers for invariants and auditing in the database, business workflows in the application, views as stable read contracts, and procedures where round trips or privilege separation genuinely matter.

In an interview

Show both sides: "I would enforce 'balance never negative' with a CHECK constraint and keep an audit trigger writing old and new values to an append-only table, because those must hold for every writer. The refund workflow lives in the service, where it is tested and observable. Reporting uses views so we can refactor tables without breaking dashboards."