Stored Functions, Procedures & Triggers — SQL Course

What you'll learn

Use server-side programmability selectively for data-local operations, while controlling volatility, side effects, recursion, and deployment complexity. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply SQL and PL/pgSQL functions to a realistic data question
Apply Procedures to a realistic data question
Apply Triggers to a realistic data question
Apply OLD and NEW to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Function volatility	Whether repeated calls can change or observe changing state	Declare VOLATILE, STABLE, or IMMUTABLE truthfully for planner safety
Trigger	Code fired by a table event	Use for local invariants or audit facts, not hidden distributed workflows
SECURITY DEFINER	Execution with function-owner authority	Lock search_path, minimize owner privileges, and revoke public execute

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the database programmability boundary question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Audit price changes locally

The trigger records old and new values in the same transaction without calling external systems.

price_audit_trigger.sql

CREATE FUNCTION audit.log_price_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO audit.product_price_changes(product_id, old_price, new_price, changed_by)
    VALUES (NEW.id, OLD.price, NEW.price, current_user);
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON inventory.products
FOR EACH ROW EXECUTE FUNCTION audit.log_price_change();

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Triggers hide work from the statement caller and can recurse or surprise bulk operations. Keep them small, documented, and tested.

Independent workshop

Build a review-ready database programmability boundary lab against the course commerce dataset.

Your finished workshop must include:

SQL and PL/pgSQL functions
Procedures
Triggers
OLD and NEW
Volatility
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Function volatility: Declare VOLATILE, STABLE, or IMMUTABLE truthfully for planner safety
Trigger: Use for local invariants or audit facts, not hidden distributed workflows
SECURITY DEFINER: Lock search_path, minimize owner privileges, and revoke public execute

Quick check

1. Which rule best applies to Function volatility?

2. Which rule best applies to Trigger?

3. Which rule best applies to SECURITY DEFINER?

Next: Full-Text Search