Foundations — Stored procedures, triggers, functions

Before you can read the parent note, you need to see what a table is, what a "row" and "value" mean, what it means to "run code" inside a database, and what every keyword and symbol (;, //, DELIMITER, BEGIN, END, IN, OUT, RETURN, OLD, NEW, <>, CALL, SELECT) is actually pointing at. We build each one from nothing.

0. The very first picture: what a database is

Look at the figure. The red-outlined strip is one row — everything the database knows about employee #7. The single box where that row meets the salary column holds one value (61000). Hold that image; every symbol below is about reading or changing one of these boxes.

Why the topic needs this: stored code exists to read and change these boxes safely. If you can't picture a row, you can't picture what the later OLD/NEW snapshots (Section 12) refer to.

1. The three commands that change data: INSERT, UPDATE, DELETE

Before we can talk about code that reacts to changes, we must name the changes. There are exactly three ways to change the data in a table.

Picture the table from Section 0. INSERT adds a new horizontal line at the bottom; UPDATE rewrites a box inside an existing line; DELETE erases a whole line. Everything a trigger reacts to is one of these three events.

Why the topic needs this: a trigger is defined by which of these events sets it off, and its snapshots (OLD/NEW) exist or not depending on which one happened.

2. What does "run code inside the database" mean?

Normally your application (a program on another computer) sends a message to the database asking a question, and the database mails an answer back. That message travels over a network — a round trip.

The three "flavours" of code-in-the-DB (procedure, function, trigger) are just different doorways to that right-hand picture.

Why the topic needs this: every WHY in the parent note ("less network chatter", "centralized rules", "atomicity") is literally the difference between these two pictures.

3. The three flavours, defined plainly

Before any syntax, pin down what each of the three things actually is — the whole parent note is these three concepts and nothing else.

The figure lines up the three doorways: you press the procedure, you embed the function, and the event fires the trigger. Everything else on this page is the vocabulary needed to read these three.

Why the topic needs this: the entire parent note is a comparison of these three. If "runs automatically vs. you CALL vs. you embed" is fuzzy, every later distinction is fuzzy too.

4. Statements and the ; terminator

Picture a to-do list. Each line is a statement; the ; is you crossing the item off and moving to the next. The database reads until it hits ;, executes, then continues.

Why the topic needs this: the whole DELIMITER puzzle (Section 5) only makes sense once you know ; = "run it now."

5. DELIMITER, the // symbol, and setting it back

Here is the puzzle Section 4 set up. When you CREATE a procedure, its body contains its own ; on every inner line. But the database reads until the first ; and thinks the whole command is finished — chopping your procedure in half.

Once the CREATE is finished, you set the terminator back to normal so the rest of your session uses ; again:

DELIMITER //
CREATE PROCEDURE greet()
BEGIN
  SELECT 'hello';
END //
DELIMITER ;          -- put the normal terminator back

The last line, DELIMITER ;, is the "set it back" step: it re-declares ; as the end-of-command marker.

Read the figure: with the normal ; terminator (top), the database stops too early (the red cut). After DELIMITER // (bottom), the inner ; are harmless and the single // at the end marks the true finish.

Why the topic needs this: every CREATE PROCEDURE/FUNCTION/TRIGGER in the parent note is wrapped in DELIMITER // … DELIMITER ; for exactly this reason.

6. Comments: -- and /* ... */

You just saw -- put the normal terminator back. That is a comment — a note for humans that the database ignores.

Picture margin notes in a recipe: the cook reads them, but they are not steps you perform. Comments explain why a line exists without changing what runs.

Why the topic needs this: the parent note's code samples carry -- call it: and similar lines; you must know they are ignored, not executed.

7. CREATE and naming things

Think of it like adding a labelled tool to a workshop wall. Once created, the tool has a name, and you summon it by that name later. transfer, net_price, audit_salary in the parent note are all such names.

Why the topic needs this: procedures, functions, and triggers are all named saved code — CREATE is how they get saved.

8. BEGIN and END: wrapping the body

The parent note's routines all put their steps between BEGIN and END. Those two words are the fence around the body.

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
END

Picture BEGIN … END as a box drawn around a to-do list: the database treats everything inside as one grouped body to run in order. (This is exactly why the inner ; lines appear — and why we needed DELIMITER in Section 5.)

Why the topic needs this: every CREATE PROCEDURE/FUNCTION/TRIGGER in the parent note has a BEGIN … END body; without it the code has no place to live.

9. Local variables and DECLARE

Sometimes the body needs a scratch value — a temporary holder for an intermediate result. That is a local variable, and you announce it with DECLARE.

BEGIN
  DECLARE fee DECIMAL(10,2);      -- make a scratch box
  SET fee = 100 * 0.02;           -- put a value in it
  SELECT fee;                     -- use it
END

Picture a sticky note you write a number on while doing a multi-step sum, then throw away when finished. DECLARE puts up the note; SET writes on it; when END is reached, the note is discarded.

Why the topic needs this: real procedures and functions compute intermediate results; without local variables you can only pass values straight through.

10. Parameters: IN, OUT, INOUT

The figure shows the code as a box with arrows. An IN arrow points into the box (the caller pushes src, dst, amt). An OUT arrow points out (the box hands a result back). Match this to transfer(IN src INT, IN dst INT, IN amt DECIMAL(10,2)) — three arrows pointing in.

Why the topic needs this: parameters are how a procedure/function is told what to work on — without them every call would do the same fixed thing.

11. Types, RETURNS, and the RETURN statement

Picture money boxes shaped exactly to fit two digits of cents — no rounding surprises. That precise shape is why money uses DECIMAL, not the sloppy floating-point kind.

CREATE FUNCTION net_price(price DECIMAL(10,2), rate DECIMAL(4,3))
RETURNS DECIMAL(10,2)          -- the PROMISE: one DECIMAL comes out
BEGIN
  RETURN price * (1 + rate);   -- the ACT: hand that value back now
END

RETURNS (with the S) is the label at the top saying what will come out; RETURN (no S) is the statement in the body that does it. A procedure has neither, because it need not hand anything back.

Why the topic needs this: a function is defined by giving back one typed value; RETURNS declares it and RETURN fulfils it. Knowing only one leaves the loop open.

12. OLD and NEW: the two snapshots

When a trigger fires because of an INSERT, UPDATE, or DELETE (Section 1), the database offers two read-only snapshots of the row involved.

Read the figure top to bottom — all three cases:

• INSERT (a brand-new row appears): there is no "before", so only NEW exists.
• DELETE (a row disappears): there is no "after", so only OLD exists.
• UPDATE (a row is edited): both a before and after exist, so OLD and NEW both exist.

Why the topic needs this: every trigger reads its data through OLD/NEW; using the wrong one (e.g. NEW in a DELETE trigger) is the classic bug the parent note warns about. So NEW.salary <> OLD.salary means "the incoming salary differs from the previous one" — see <> next.

13. SELECT, and the <> symbol

Picture a balance scale: = is level, <> is tipped either way. The trigger uses it to ask "did the salary actually change?" and only logs when the scale is tipped.

Why the topic needs this: SELECT is where functions live and how data is read; <> is how the audit trigger's IF NEW.salary <> OLD.salary avoids logging non-changes.

14. CALL vs "inside a query"

Picture two different doorways into the "run code in the DB" room: CALL is the front door you walk through deliberately; a function slips in through the middle of a SELECT the query planner is already reading, one row at a time.

Why the topic needs this: the parent note's central distinction — "invocation context is the dividing line" — lives entirely in this difference.

15. The prerequisite map

Read the map in three streams, then watch them merge. Stream 1 (top): the data story — a row is made of values, changing a box is done by INSERT/UPDATE/DELETE, and wanting to do that inside the database is why in-DB code exists. Stream 2 (left): the syntax story — a statement ends in ;, so DELIMITER lets a body full of ; survive, CREATE names it, and BEGIN…END wraps its steps. Stream 3 (right): the per-flavour extras — procedures need parameters, functions need RETURNS/RETURN, triggers need OLD/NEW. All three streams pour into the parent topic at the bottom.

Why the topic needs this map: the parent note constantly compares the three flavours, and each comparison rests on a different foundation — a function's RETURNS/RETURN, a procedure's parameters, a trigger's OLD/NEW. When a distinction confuses you, the map tells you exactly which earlier box to re-read.

For the layers underneath this topic, see Transactions and ACID (why the money-transfer must be all-or-nothing), SQL Queries (SELECT, JOIN) (the queries functions plug into), Database Normalization and Indexes (what triggers and deterministic functions can maintain), and Concurrency Control (the locks this code runs under). Then return to the parent: the full topic.

Equipment checklist

Cover the right side and check you can answer each before reading the parent note.