SovranCode
SQL: Query, Model, and Analyze Data Stored procedures, functions, and triggers
This device
Course contentsStored procedures, functions, and triggers · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONPreventing SQL injection
NEXT LESSONProduction data project
Security and production workflow · Lesson 31 155 min

Stored procedures, functions, and triggers

A table stores facts. A view stores a SELECT. A function stores a calculation you can use in SQL. A procedure stores a named workflow you CALL. A trigger stores a reaction the engine fires on a write. You pick the object that matches the job, then keep the SQL in source control—last lesson's parameters still apply inside every body.

SQLite in this runner

SQLite has CREATE TRIGGER, generated columns, and views. It does not have CREATE PROCEDURE or SQL-bodied CREATE FUNCTION. PostgreSQL and SQL Server do. You will run the objects SQLite can store, and read the PostgreSQL shape so the names still mean something at work.

Three names, three jobs

A function answers a question: given cents, return dollars; given a sku, return tax. You use it in SELECT, WHERE, or an index. A procedure does work: restock pens, close a month, copy rows. You CALL it on purpose. A trigger is not called. An INSERT, UPDATE, or DELETE happens, and the engine runs extra SQL you attached to that event.

If the next developer should see the extra work, prefer a procedure or application function they invoke by name. If the rule must hold even when someone writes a one-line INSERT in a console, a trigger (or a CHECK) is the backstop. Hidden magic is a cost. Use it when the cost of forgetting is higher.

PostgreSQL names look like this (do not paste them into this runner):

CREATE FUNCTION listed_dollars(cents integer)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$ SELECT round(cents / 100.0, 2) $$;

CREATE PROCEDURE restock(p_sku text, n integer)
LANGUAGE sql
AS $$
  UPDATE products SET stock = stock + n WHERE sku = p_sku;
$$;

CALL restock('PEN-2', 12);
SQL BROWSER RUNNER

List tables, a view, and a trigger

sqlite_master type is table, view, or trigger. There is no procedure row.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE VIEW catalog_store AS
SELECT sku, name
FROM products;

CREATE TABLE product_audit (
  sku TEXT NOT NULL,
  action TEXT NOT NULL
);

CREATE TRIGGER trg_audit_product AFTER INSERT ON products
BEGIN
  INSERT INTO product_audit (sku, action) VALUES (NEW.sku, 'insert');
END;

SELECT type, name
FROM sqlite_master
WHERE type IN ('table', 'view', 'trigger')
  AND name NOT LIKE 'sqlite_%'
ORDER BY type, name;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which types appear, and which CREATE would this engine reject?

Functions and views calculate; they do not hide writes

Where PostgreSQL would CREATE FUNCTION, SQLite gives you a generated column or a view. listed_dollars is always cents divided by 100. product_tax is always eight percent, rounded. Both stay in the same transaction as the read. Neither decrements stock. If you need a write, that is a procedure, a trigger, or application SQL.

Generated columns can be VIRTUAL (computed on read) or STORED (written on the row). Views stay live, as in the views lesson. Application-defined functions exist in SQLite too, but they are registered from the host language, not from CREATE FUNCTION in this box.

SQL BROWSER RUNNER

Compute dollars and tax without CREATE FUNCTION

VIRTUAL generated column plus a view. No writes except the seed INSERT.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  listed_dollars REAL GENERATED ALWAYS AS (listed_cents / 100.0) VIRTUAL
);

INSERT INTO products (sku, name, listed_cents) VALUES
  ('NB-1', 'SQL notebook', 1200),
  ('PEN-2', 'Gel pen', 400);

CREATE VIEW product_tax AS
SELECT
  sku,
  listed_cents,
  CAST(ROUND(listed_cents * 0.08) AS INTEGER) AS tax_cents
FROM products;

SELECT sku, listed_cents, listed_dollars FROM products;
SELECT sku, tax_cents FROM product_tax;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is listed_dollars for NB-1, and what is tax_cents for PEN-2?

AFTER INSERT is a reaction, not a second statement you remember

You insert ink. The trigger inserts an audit row. The application only wrote one statement. That is the point and the hazard: a later INSERT still fires, including one from a migration or a console. Name triggers so sqlite_master reads like a checklist. Keep the body short. Bind values in the host; inside the trigger, NEW.sku is already a value, not a string you concatenate.

SQL BROWSER RUNNER

Audit every product INSERT

AFTER INSERT copies NEW.sku into product_audit.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TABLE product_audit (
  sku TEXT NOT NULL,
  action TEXT NOT NULL
);

CREATE TRIGGER trg_audit_product AFTER INSERT ON products
BEGIN
  INSERT INTO product_audit (sku, action) VALUES (NEW.sku, 'insert');
END;

INSERT INTO products VALUES ('INK-4', 'Ink', 250, 10);

SELECT sku, action FROM product_audit;
SELECT sku, stock FROM products WHERE sku = 'INK-4';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many audit rows, and which sku did NEW carry?

BEFORE plus RAISE is a CHECK that can see more

CHECK (listed_cents >= 0) is the first tool. A BEFORE INSERT trigger is for rules that need a message, another table, or a condition CHECK cannot express. RAISE(ABORT, '...') stops the statement. SQLite also has FAIL, ROLLBACK, and IGNORE. Prefer ABORT unless you have a reason. This runner stops on the first error, so the starter insert is valid. Change cents to -1 when you want to see the abort; put that INSERT last, or run it alone.

SQL BROWSER RUNNER

Reject a negative price before the row exists

BEFORE INSERT RAISE when NEW.listed_cents is negative. Starter insert is 250.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TRIGGER trg_price_nonneg BEFORE INSERT ON products
BEGIN
  SELECT RAISE(ABORT, 'listed_cents must be >= 0')
  WHERE NEW.listed_cents < 0;
END;

INSERT INTO products VALUES ('INK-4', 'Ink', 250, 10);

SELECT sku, listed_cents FROM products WHERE sku = 'INK-4';

-- Change 250 to -1 on the INSERT above and Run again to see RAISE ABORT.
-- This runner stops on the first error, so keep the successful INSERT last while you read the table.
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which sku landed, and what happens if you insert listed_cents -1 instead?

UPDATE has both OLD and NEW

On UPDATE, OLD is the row as it was, NEW is the row as it will be. UPDATE OF listed_cents means SQLite does not even enter the trigger when you only change name. Pair that with a WHEN clause so a no-op price change does not log a fake event. There is no OLD on INSERT and no NEW on DELETE.

SQL BROWSER RUNNER

Log the previous and next price

AFTER UPDATE OF listed_cents writes OLD and NEW into price_log.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TABLE price_log (
  sku TEXT NOT NULL,
  old_cents INTEGER NOT NULL,
  new_cents INTEGER NOT NULL
);

CREATE TRIGGER trg_log_price AFTER UPDATE OF listed_cents ON products
BEGIN
  INSERT INTO price_log (sku, old_cents, new_cents)
  VALUES (NEW.sku, OLD.listed_cents, NEW.listed_cents);
END;

UPDATE products SET listed_cents = 450 WHERE sku = 'PEN-2';

SELECT sku, old_cents, new_cents FROM price_log;
SELECT sku, listed_cents FROM products WHERE sku = 'PEN-2';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What were old_cents and new_cents for PEN-2?

WHEN skips work you do not need

The first UPDATE sets the price to the same 400. WHEN NEW.listed_cents <> OLD.listed_cents skips the log. The name change does not fire UPDATE OF listed_cents at all. The third statement changes 400 to 450 and writes one log row. That is how you keep triggers from flooding a table.

SQL BROWSER RUNNER

Log only a real price change

Same 400, then a name-only edit, then 450. One log row.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TABLE price_log (
  sku TEXT NOT NULL,
  old_cents INTEGER NOT NULL,
  new_cents INTEGER NOT NULL
);

CREATE TRIGGER trg_log_price AFTER UPDATE OF listed_cents ON products
WHEN NEW.listed_cents <> OLD.listed_cents
BEGIN
  INSERT INTO price_log (sku, old_cents, new_cents)
  VALUES (NEW.sku, OLD.listed_cents, NEW.listed_cents);
END;

UPDATE products SET listed_cents = 400 WHERE sku = 'PEN-2';
UPDATE products SET name = 'Gel pen (retail)' WHERE sku = 'PEN-2';
UPDATE products SET listed_cents = 450 WHERE sku = 'PEN-2';

SELECT sku, old_cents, new_cents FROM price_log;
SELECT sku, name, listed_cents FROM products WHERE sku = 'PEN-2';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many price_log rows, and which listed_cents and name remain on PEN-2?

INSTEAD OF turns a view into a write API

Storefront clients should not choose listed_cents. A view of sku, name is the interface. Ordinary views reject INSERT. INSTEAD OF INSERT runs your body: you write the base table and pick the defaults (here, cents 0 and stock 0). PostgreSQL uses the same idea on views and, with rules or triggers, on more complex APIs. The view is still not a table. SELECT reads through it; writes go through the trigger you wrote.

SQL BROWSER RUNNER

Insert through a view with INSTEAD OF

catalog_write has two columns. The trigger fills listed_cents and stock.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE VIEW catalog_write AS
SELECT sku, name FROM products;

CREATE TRIGGER trg_catalog_insert INSTEAD OF INSERT ON catalog_write
BEGIN
  INSERT INTO products (sku, name, listed_cents, stock)
  VALUES (NEW.sku, NEW.name, 0, 0);
END;

INSERT INTO catalog_write (sku, name) VALUES ('INK-4', 'Ink');

SELECT sku, name FROM catalog_write WHERE sku = 'INK-4';
SELECT sku, listed_cents, stock FROM products WHERE sku = 'INK-4';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What cents and stock did INK-4 get, and who chose those defaults?

A stock decrement belongs in the same write as the order

If the application inserts an order and forgets to decrement, the catalog lies. A trigger on orders makes the pair atomic: one statement, two tables, one transaction. That is the honest use. The dishonest use is a web of triggers that update each other. SQLite recursive triggers are off unless you PRAGMA recursive_triggers = ON. Leave them off until you can draw the graph on paper.

Still prefer an application transaction that inserts the order and updates stock when the rule is a product workflow you want tests to name. Use the trigger when every writer—including a future intern’s console—must obey.

SQL BROWSER RUNNER

Decrement stock when an order lands

AFTER INSERT on orders subtracts NEW.qty from products.stock.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TRIGGER trg_dec_stock AFTER INSERT ON orders
BEGIN
  UPDATE products
  SET stock = stock - NEW.qty
  WHERE sku = NEW.sku;
END;

INSERT INTO orders (order_id, sku, qty) VALUES (1, 'PEN-2', 1);

SELECT order_id, sku, qty FROM orders;
SELECT sku, stock FROM products WHERE sku = 'PEN-2';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is PEN-2 stock after qty 1, and why is that safer than a second statement you might forget?

Keep CALL-style work in the application on SQLite

“Restock 12 pens on Tuesday” is a procedure: someone decides to run it. There is no Tuesday trigger. Write UPDATE products SET stock = stock + ? WHERE sku = ? in the app, with last lesson’s binds and a clerk login from the roles lesson. Putting that in a trigger on SELECT is impossible; putting it on a dummy table is theatre. Match the object to the verb: calculate, call, or react.

What you should be able to do

  • Tell a function from a procedure from a trigger in one sentence each.
  • Create an AFTER INSERT audit trigger using NEW.
  • Abort a bad INSERT with BEFORE and RAISE.
  • Log OLD and NEW on UPDATE, with WHEN and UPDATE OF.
  • Write INSTEAD OF INSERT on a view.
  • Know that SQLite has no CREATE PROCEDURE and what you use instead.

Independent lab: audit, stock, and a price guard

The starter dumps stock. Add three triggers: reject negative listed_cents, audit product inserts, decrement stock from orders.qty. Insert a valid product, place one order for PEN-2 quantity 1, and show audit plus remaining stock. Optionally add a BEFORE INSERT ON orders that RAISEs when NEW.qty is greater than current stock—run that attempt last so a failure does not hide the successful selects.

SQL BROWSER RUNNER

Attach write-time rules to the catalog

You write the triggers. Seed data is already here.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

CREATE TABLE product_audit (
  sku TEXT NOT NULL,
  action TEXT NOT NULL
);

-- Add: BEFORE INSERT RAISE if listed_cents < 0
-- Add: AFTER INSERT into products → product_audit
-- Add: AFTER INSERT into orders → decrement products.stock
-- Then INSERT a valid product, INSERT one order for PEN-2 qty 1,
-- and SELECT audit, PEN-2 stock, and orders.

SELECT sku, stock FROM products;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which audit rows exist, what is PEN-2 stock after one sale, and what RAISE would stop qty 99?

Common mistakes to avoid

  • Writing CREATE PROCEDURE in SQLite and assuming the server stored it.
  • Using a trigger for a one-off admin task that should be CALL or application SQL.
  • Forgetting NEW versus OLD, or expecting OLD on INSERT.
  • Logging every UPDATE including no-op price changes.
  • Trigger A updates a table that fires trigger A again, untested.
  • Putting business rules only in triggers so ORMs and consoles disagree about what a row means.
  • Concatenating SQL inside a trigger body—the same injection lesson, smaller room.
  • Using INSTEAD OF and also expecting the view to store its own rows.

Lesson review

  • I can choose function, procedure, trigger, or application code for a job.
  • I can CREATE TRIGGER with NEW, OLD, WHEN, and UPDATE OF.
  • I can RAISE(ABORT) from BEFORE INSERT when CHECK is not enough.
  • I can write INSTEAD OF INSERT on a view.
  • I can keep stock and audit in the same transaction as the write that requires them.
  • I can explain what SQLite lacks compared with PostgreSQL routines.
KNOWLEDGE CHECK

Check procedures, functions, and triggers

Answer all ten questions, then reopen the runner whose trigger, view, or generated column still feels unclear.

01What is a trigger?
02What is a stored procedure, in engines that have one?
03How does a function differ from a procedure?
04Which object does SQLite actually store for automatic write-time behavior?
05What are NEW and OLD?
06Why add a WHEN clause on a trigger?
07What does INSTEAD OF mean on a view?
08When is a BEFORE INSERT trigger with RAISE(ABORT, ...) the right tool?
09What is the main risk of putting business rules only in triggers?
10Where should a one-off “restock 12 pens” workflow live if you are on SQLite?
PREVIOUS LESSONPreventing SQL injection
NEXT LESSONProduction data project
ON THIS PAGEThree jobsFunctions and viewsAFTER INSERTBEFORE and RAISEOLD and NEWWHENINSTEAD OFStock triggerIndependent labCommon mistakesKnowledge check
Course contents