SQL Tutorial 11: Stored Procedures, Triggers, and User-Defined Functions

Sooner or later you open a database and find logic that is not a table and not a simple view. A stored procedure runs a scripted routine inside the engine. A trigger fires automatically when rows change. A user-defined function (UDF) packages a calculation you can call from SQL. These tools can protect integrity and centralize rules. They can also hide business logic where analysts cannot review it in git, and where one engine’s dialect will not run on another. This part is about knowing what you are looking at and when to care.

This is Part 11 of the SQL series. Parts 9 and 10 covered views, indexes, CTEs, and windows: the everyday analytical toolkit. Now you meet procedural objects that live beside the data. The series map is on the SQL series page. Broader paths sit on Learn.

What you will learn

• Plain definitions of procedures, triggers, and UDFs
• When each appears in operational systems versus analytics warehouses
• Why many analytics teams prefer SQL scripts, scheduled jobs, or dbt-style models
• Portability and debugging cautions for multi-engine careers
• How an analyst should investigate hidden logic without rewriting the app
• A simple conceptual example shape (dialect-light) on the toy domain

Three objects on one map

Example output:

Stored procedures: named routines in the database

A stored procedure is a stored program you invoke with a CALL (or engine-specific execute syntax). It can accept parameters, run several statements, handle errors, and return result sets or status codes. Application backends and older enterprise systems love them for encapsulating “create order with lines and inventory check” style workflows close to the data.

Conceptual shape (not one portable standard dialect):

-- Pseudocode-style illustration; syntax differs by engine
-- PROCEDURE refresh_customer_order_stats(p_as_of_date DATE)
-- BEGIN
--   DELETE FROM customer_order_stats WHERE as_of_date = p_as_of_date;
--   INSERT INTO customer_order_stats (...)
--   SELECT customer_id, COUNT(*), SUM(amount), p_as_of_date
--   FROM orders
--   WHERE status = 'paid' AND order_date <= p_as_of_date
--   GROUP BY customer_id;
-- END;

That routine rebuilds a stats table for a date. The idea is useful. The implementation language (PL/pgSQL, T-SQL, PL/SQL, etc.) is not interchangeable. If your career spans Postgres today and a cloud warehouse tomorrow, procedure code rarely travels for free.

When procedures help

• Multi-step writes that must be atomic and close to tables with tight constraints
• Legacy applications designed around procedure APIs
• Controlled batch jobs already owned by a DBA team with monitoring around them

When analysts should pause

• Business metric definitions only exist inside procedural code nobody diffs in pull requests
• You need the same transform on Snowflake, BigQuery, and a local DuckDB file
• Debugging requires privileges and tools you do not have during a dashboard fire drill

Many modern analytics shops prefer SQL scripts in version control, orchestrated by a scheduler, or models compiled by tools such as dbt. Those approaches keep logic reviewable, testable, and closer to the way analysts already work with SELECT. Procedures still exist in OLTP systems you will query. Prefer not to invent new ones for pure analytics transforms unless your platform standard says so.

Triggers: automatic reactions to row changes

A trigger is logic attached to a table event: before or after insert, update, or delete. Example jobs: write an audit row when amount changes, block deletes of customers with open orders, maintain a denormalized summary column, or sync a search index table.

Conceptual illustration:

-- Pseudocode: after update of orders.amount, log the change
-- TRIGGER trg_orders_amount_audit
-- AFTER UPDATE ON orders
-- FOR EACH ROW
-- WHEN (OLD.amount IS DISTINCT FROM NEW.amount)
-- BEGIN
--   INSERT INTO orders_audit (order_id, old_amount, new_amount, changed_at)
--   VALUES (NEW.order_id, OLD.amount, NEW.amount, CURRENT_TIMESTAMP);
-- END;

From an analyst’s seat, triggers explain mysteries: “I updated one column and three other tables moved.” They also create load: every write pays the trigger cost. Cascading triggers (A fires B fires C) can produce surprising performance and surprising data lineage.

How to investigate as an analyst

• Ask whether the table has triggers; check information schema or engine catalogs if you have access.
• Reproduce a small write in a safe environment and watch related tables.
• Document side effects in your metric notes (“status flips also write to audit_x”).
• Do not disable triggers casually in shared environments.

Part 8’s write safety still applies. Triggers can amplify a bad UPDATE across side tables. Preview, stage, and transact carefully when you have write rights at all.

User-defined functions: package a calculation

A UDF returns a value (or, in some systems, a table) and can be used in SELECT lists, WHERE clauses, and other expression slots depending on the engine. Simple SQL-bodied functions might convert units, normalize phone formats, or compute a business score from a few columns.

-- Pseudocode SQL-style function
-- FUNCTION order_band(p_amount NUMERIC) RETURNS TEXT AS
-- BEGIN
--   RETURN CASE
--     WHEN p_amount >= 200 THEN 'high'
--     WHEN p_amount >= 50 THEN 'mid'
--     ELSE 'low'
--   END;
-- END;

SELECT
  order_id,
  amount,
  order_band(amount) AS amount_band
FROM orders
WHERE status = 'paid';

You could write the same CASE inline. A function helps when many queries need the identical banding rules. Costs to remember:

• Opaque functions can prevent index use or predicate pushdown if the planner cannot see through them.
• Language choice matters: SQL functions are often more optimizer-friendly than black-box procedural or external language functions.
• Portability is weak across engines and warehouses.
• Versioning can be painful if production functions drift from what documentation claims.

For analytics warehouses, teams often encode banding as a column in a curated model or a shared macro in a transformation tool, so the logic is visible in code review. Same intent as a UDF, different packaging.

Where analysts should put logic (a practical bias)

There is no universal law, but there is a common modern bias for analytics work:

If you are choosing defaults for a new analytics warehouse, start with declarative SQL models, tests, and documentation. Reach for procedures when your platform and platform owners standardize on them. Reach for triggers when write-time enforcement is the product requirement, not when you want a sneaky ETL.

This bias matches how many teams already partner SQL with Python: keep durable transforms near the warehouse in SQL, use Python for awkward reshaping and modeling as in the Python series, and keep quality checks explicit as in data quality.

None of this means procedures are “bad.” It means the default for analytics definition should optimize for review, test, and move-across-engines. A payments platform might correctly keep authorization workflows in tightly controlled procedures next to balances. A marketing analytics warehouse almost certainly should not hide “active subscriber” inside an undocumented function only three people can edit. Match the tool to the risk and the audience that must maintain the meaning.

When you interview for analytics roles, expect questions that sound like Part 11 even if nobody says “trigger.” “Where does this metric live?” “Can we change it safely?” “What runs when a row updates?” Your ability to answer with calm inventory language is more valuable than reciting CREATE TRIGGER syntax from memory.

Portability caution (career skill, not pedantry)

SELECT with joins and aggregations is relatively portable across engines with modest dialect edits. Procedural code is not. Even “SQL standard” features arrive unevenly. When you invest weeks in trigger trees and proprietary functions, you are investing in a specific platform. That can be the right call for a stable OLTP core. It is a riskier call for analytics logic that leadership may migrate to a new warehouse next year.

Portable habits:

• Prefer declarative SQL for transforms you might rerun elsewhere.
• Keep business definitions in documented models, not only in compiled procedures.
• When you must use engine-specific features, isolate them and comment the dependency.
• Test restores and migrations with real object inventories (Part 13 mindset).

Worked story: the missing refunds audit

You filter orders for status changes and notice amounts sometimes differ from yesterday’s extract even when status did not change. A teammate mentions “the audit trigger.” You request catalog access and find a trigger logging amount edits into orders_audit. Your corrected analysis becomes:

SELECT
  a.order_id,
  a.old_amount,
  a.new_amount,
  a.changed_at,
  o.status,
  o.customer_id
FROM orders_audit AS a
INNER JOIN orders AS o
  ON o.order_id = a.order_id
WHERE a.changed_at >= '2024-06-01'
ORDER BY a.changed_at DESC;

You did not rewrite the trigger. You learned the side table existed, joined it, and answered the real question: which amounts moved, when, and for which customers. That is professional analyst behavior around procedural objects: discover, document, query the effects, escalate changes to owners.

If no audit table exists and amounts still drift, look for batch procedures that rewrite rows overnight, application jobs, or manual DML. Procedural objects are one class of hidden writers, not the only class.

Security and least privilege

Procedures can run with definer rights or invoker rights depending on the engine and how they were created. That means calling a procedure might touch tables you cannot query directly, or might fail even when you can read the tables. Triggers run with privileges that can surprise you. UDFs might be allowed in some roles and not others. As an analyst, do not treat “I can SELECT” as “I understand every write path.” Ask owners for a data flow sketch when numbers move without an obvious ETL repo.

Documenting inherited procedural logic

When you inherit a system full of procedures and triggers, your first job is not to rewrite it in dbt by Friday. Your first job is a map. Create a living note (wiki, README, ticket epic) with:

• Object name, type (procedure, trigger, function), and host schema
• Tables read and tables written
• Who or what invokes it (app endpoint, scheduler, manual DBA, nested call)
• Business purpose in one sentence
• Last known owner and last change date if available
• Risk notes (cascading triggers, rights elevation, non-obvious side tables)

That inventory prevents two failure modes: accidental double-processing (you build a pipeline that repeats what a nightly procedure already does) and accidental blindness (you report from a table that a trigger rewrites after your extract). Share the inventory with platform owners. Ask which objects are sacred, which are legacy, and which are safe to replace with declarative models over a planned migration.

If you propose a migration, migrate meaning first: encode the metric or transform as a tested SQL model, compare outputs against the procedure for a parallel run period, then cut consumers over. Do not delete procedural objects until the last consumer and the last write path are accounted for. Part 13’s maintenance mindset applies: backups, grants, and runbooks before hero rewrites.

Common mistakes

• Putting metric definitions only inside procedures so nobody can unit test them in CI.
• Trigger cascades that turn one update into a performance cliff.
• Assuming UDF results are free in large scans; measure.
• Copy-pasting procedural syntax across engines and declaring SQL “broken.”
• Debugging production triggers live without a staging clone.
• Ignoring that application code may duplicate the same rules, so fixing only one side creates drift.

Rule of thumb: Use the database to enforce hard integrity. Use versioned SQL models to define analytics meaning. Use procedures when your system of record already standardizes on them, not because they sound advanced.

How to practice

• On a local engine that supports them, create a tiny audit table and a trigger that logs amount changes on orders. Update one row and query the audit.
• Rewrite the amount banding CASE as both an inline expression and, if available, a simple function. Compare readability and EXPLAIN if you can.
• Sketch a one-page inventory: tables, views, known jobs, suspected triggers. Leave blanks where you must ask owners.
• Convert a multi-step notebook transform into a plain SQL script file with comments. Notice how much clearer review becomes without procedural wrappers.
• Read your engine’s docs for procedure and trigger syntax once, so vendor errors feel less alien.

Spreadsheet analogy: a trigger is a bit like a sheet script that fires on edit, with all the same “who changed my cell?” energy. Habits from From spreadsheets to real data about uncontrolled automation still apply at database scale.

Quick recap

• Stored procedures are callable routines; triggers fire on table events; UDFs package expressions.
• Analysts should discover and document these objects because they change lineage and numbers.
• Many analytics teams prefer versioned SQL scripts and models over new procedural metric logic.
• Portability and optimizer transparency favor declarative SQL for transforms.
• Integrity enforcement at write time can still justify triggers and procedures in OLTP systems.

Leave this part with a calm stance: respect procedural objects in systems of record, prefer declarative versioned SQL for analytics meaning, and document what you inherit before you rewrite it. That stance keeps you useful in both modern warehouses and older operational databases without turning every problem into a trigger.

Part 12 turns to best practices and optimization: EXPLAIN mindset, select lists, sargable filters, and index awareness without premature complexity. Continue on the SQL series.

Sources

Research and further reading used for this article:

• PostgreSQL documentation: User-defined functions
• PostgreSQL documentation: Triggers
• PostgreSQL documentation: CREATE PROCEDURE
• MySQL Reference Manual: Stored programs and views
• Microsoft Learn: Stored procedures (Database Engine)
• Analytics Made Simple: SQL series

More definitions in Key Terms →

Merch on Etsy

Open store →

Imbalanced Classes Ceramic Coffee Mug View on Etsy → ETL Data Science Shirt View on Etsy → Sigmoid Function Shirt View on Etsy → Apache Kafka T-Shirt View on Etsy →