Views, Stored Procedures and Triggers

7 pages · ~5 min✓ Reviewed

Builds on ER Model and Normalization. Next up: Indexing and Storage.

DBMSPage 1

Views: The Mental Model

1. What a View Is

A view is a named, stored SELECT
It holds no data of its own
Each query on it re-runs the underlying query

sql

CREATE VIEW active_users AS
  SELECT id, name FROM users WHERE active = 1;
SELECT * FROM active_users;

define once, query like a table

Outer queryuses the view
Inliningoptimizer merges
Base tablesone plan

2. Updatable Views

INSERT/UPDATE/DELETE pass through to the base table
Merged into the outer query, so a view is not a cache

View query has	Updatable?
Single base table, plain columns	Yes
DISTINCT, GROUP BY, aggregates	No
UNION or window functions	No

3. Mistakes

Stacked views

Views on views hide cost and make plans unreadable. CREATE OR REPLACE VIEW can add columns, not drop or reorder them.

DBMSPage 2

Updatable Views and Security via Views

1. WITH CHECK OPTION

WITH CHECK OPTION rejects INSERT or UPDATE through the view that would give a row the view cannot show

sql

CREATE VIEW eu_orders AS
  SELECT * FROM orders
  WHERE region='EU' WITH CHECK OPTION;

INSERT INTO eu_orders (region)
  VALUES ('US');  -- rejected

region='US' fails the view's WHERE

2. Security via Views

GRANT SELECT on the view, REVOKE on the base table
View exposes only chosen columns and rows
Column hiding: emp_public leaves out salary

sql

CREATE VIEW emp_public AS
  SELECT id, name, dept FROM employees;
REVOKE ALL ON employees FROM analyst;
GRANT SELECT ON emp_public TO analyst;

3. PostgreSQL Mistakes

	Default	`security_invoker = true`
Runs with	view owner's privileges	caller's privileges

Leaky function in the view's WHERE can expose hidden rows
Fix: create the view with security_barrier

DBMSPage 3

Materialized Views and Refresh

1. What it stores

Stores the query result physically on disk
Reads are fast: no recompute per query
Data can be stale until refreshed
Can be indexed like a table

sql

CREATE MATERIALIZED VIEW daily_sales AS
SELECT day, SUM(amt) total
FROM sales GROUP BY day;

2. Refresh

REFRESH MATERIALIZED VIEW (PostgreSQL) recomputes everything and locks reads
Add CONCURRENTLY to avoid the read lock
CONCURRENTLY needs a UNIQUE index on the view
Oracle: fast (incremental) refresh via materialized view logs

sql

CREATE UNIQUE INDEX ON daily_sales (day);
REFRESH MATERIALIZED VIEW daily_sales;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

3. View vs materialized view

	View	Materialized view
Data	stored query only	stored result
Freshness	always fresh

DBMSPage 4

Stored Procedures and Functions

1. Procedure vs Function

Stored procedure: named, precompiled SQL block kept in the DB
Run standalone: CALL archive_orders(30);
Parameters: IN, OUT, INOUT
Can control transactions: COMMIT / ROLLBACK
Function: returns a value or table, used inside SELECT
Example: SELECT tax(amount) FROM orders;

	Function	Procedure
Called	Inside an expression	Standalone with `CALL`
Returns	Value or table	Nothing required; `OUT` params
Transactions	Usually cannot commit	May COMMIT / ROLLBACK

2. Example (PostgreSQL)

sql

CREATE FUNCTION tax(a numeric)
RETURNS numeric AS $$
  SELECT a*0.18
$$ LANGUAGE sql;

then: SELECT tax(amount) FROM orders;

3. Common Mistake

Row-by-row loops or cursors inside a procedure are slow

DBMSPage 5

Triggers: BEFORE, AFTER, Row vs Statement

1. What a Trigger Is

Code that fires automatically on INSERT, UPDATE or DELETE of a table
Cannot be called directly, takes no arguments from the caller

2. BEFORE vs AFTER, ROW vs STATEMENT

	Runs	Use for
BEFORE	before the write; can change `NEW` or cancel	validation, defaults
AFTER	after the write	audit logs, cascading changes
`FOR EACH ROW`	once per affected row; has `OLD` and `NEW`	per-row checks
`FOR EACH STATEMENT`	once per statement, even for 0 rows; no row access	statement-level logging

3. Example

sql

CREATE TRIGGER audit_sal
AFTER UPDATE ON emp
FOR EACH ROW
EXECUTE FUNCTION log_change();

log_change() records OLD.salary and NEW.salary

4. Mistakes

Trigger that modifies its own table can recurse

DBMSPage 6

Database Logic Trade-offs and Cheat Sheet

1. DB or App?

	Keep in the DB	Keep in the app
Rules	Integrity rules, constraints	Business workflows
Data work	Audit trails	External calls (APIs, email)
Volume	Set operations on many rows	Logic needing unit tests
Why	No rows over the network	Version control, easy debug

Harder to test and debug
Tied to one vendor dialect: PL/pgSQL, PL/SQL, T-SQL
Scales only with the database server

2. Object Cheat Sheet

Object	Remember
View	Saved query, no data
Materialized view	Stored result + `REFRESH`

DBMSPage 7

Check yourself

eu_orders is a view on orders WHERE region='EU' with WITH CHECK OPTION. What does INSERT INTO eu_orders (region) VALUES ('US') do?

It is rejected: the new row would be invisible through the view.
Without the option, it would insert into orders and vanish from the view.

A new sale is inserted into sales. You query the materialized view daily_sales. Is the sale in the totals?

No. It stays stale until REFRESH MATERIALIZED VIEW.
A plain view would include it, because it re-runs the query each time.

An UPDATE matches zero rows of emp. How often do a FOR EACH ROW trigger and a FOR EACH STATEMENT trigger fire?

Row trigger: 0 times, since there are no rows.
Statement trigger: once, even for zero rows.