Worked examples — Stored procedures, triggers, functions

The scenario matrix

Before any example, let us list every distinct situation. Think of it like a checklist: each row is a "shape of problem" this topic produces, and we will hit every single one.

Below, each figure is drawn on a chalkboard so you can see which pseudo-rows are alive at which moment.

Case A — the INSERT trigger (only NEW exists)

Steps.

1. Decide the timing → BEFORE INSERT. Why this step? We want to change the row before it is written. Only a BEFORE trigger lets you assign to NEW.col; an AFTER trigger sees a row that is already committed and read-only.
2. Decide which pseudo-row → NEW only. Why this step? On INSERT the row is being born; there is no previous version, so OLD does not exist (using it would error). Look at the board: the OLD slot is crossed out.
3. Write the assignment.

DELIMITER //
CREATE TRIGGER stamp_created
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END //
DELIMITER //

4. The IF ... IS NULL guard. Why this step? If the app did supply a time, respect it; only fill the blank. This makes the trigger idempotent-ish and non-surprising.

Case B — the UPDATE trigger (both OLD and NEW)

Steps.

1. To block, we must act before the write → BEFORE UPDATE. Why? Once the row is written (AFTER) it is too late to refuse. To raise an error we use SIGNAL.
2. Compare OLD.salary with NEW.salary. Why this step? On UPDATE, both pseudo-rows exist — OLD is the previous value, NEW is the incoming one. The board shows both slots lit.
3. Block if smaller.

DELIMITER //
CREATE TRIGGER guard_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Salary cut not allowed';
  END IF;
END //
DELIMITER //

4. (Logging the raise would go in a separate AFTER UPDATE trigger, since AFTER is where you "react once final" — that is Case D below.)

Case C — the DELETE trigger (only OLD exists)

Steps.

1. Timing → BEFORE DELETE (or AFTER; both work here since we only read). Why? We just need to read the vanishing row and copy it — no need to alter it.
2. Read from OLD only. Why this step? On DELETE there is no incoming row — nothing new is being written — so NEW does not exist. The board crosses out NEW. This is the exact trap: "I'll use NEW to see what got deleted" is wrong.

DELIMITER //
CREATE TRIGGER archive_customer
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO customers_archive(id, name, deleted_at)
  VALUES (OLD.id, OLD.name, NOW());
END //
DELIMITER //

Case D — BEFORE vs AFTER: the timing decision

Steps.

1. Task (i) uppercases NEW.code → needs to modify the row. Why this fails in AFTER? In an AFTER trigger the row is already written to disk; NEW is read-only. Assigning SET NEW.code = UPPER(NEW.code) in AFTER → error.
2. Task (ii) inserts into a different table → pure reaction, no modification of the current row. Why this is fine in AFTER? You are not changing the row that fired the trigger; you're writing elsewhere.
3. Correct split:

-- (i) MUST be BEFORE
CREATE TRIGGER upcase_code
BEFORE INSERT ON products
FOR EACH ROW SET NEW.code = UPPER(NEW.code);
 
-- (ii) belongs AFTER
CREATE TRIGGER log_insert
AFTER INSERT ON products
FOR EACH ROW
  INSERT INTO product_log(code, at) VALUES (NEW.code, NOW());

Case E — degenerate multi-row: FOR EACH ROW on 0, 1, N rows

Steps.

1. FOR EACH ROW means the body runs once per affected row. Why this matters? A single UPDATE statement can touch many rows; the trigger is not "per statement." 50 affected rows → 50 executions.
2. Guard IF NEW.salary <> OLD.salary. Why this step? Only log real changes. Here every salary changes ($\times 1.10$), so all 50 pass the guard.
3. Degenerate case: dept='ghost' matches 0 rows → the trigger fires 0 times → 0 log rows. Why? No rows affected means no per-row iterations. This is the "zero input" cell — the trigger silently does nothing, which is correct.

Case F — function: the DETERMINISTIC promise (and its trap)

Steps.

1. A function is deterministic iff same inputs always give same output. Why this definition? The optimizer may cache the result or use it in a functional index (see Indexes). That is only safe if the output can't drift.
2. net_price(100, 0.18). Why deterministic? Its output depends only on its two arguments. $100\times (1+0.18)=118.00$ today, tomorrow, always. Honest to mark DETERMINISTIC.
3. age_days(d) reads NOW(). Why NOT deterministic? Same d, different day → different answer. If you lie and mark it DETERMINISTIC, the engine may cache yesterday's value and hand you a stale, silently-wrong number.

CREATE FUNCTION net_price(price DECIMAL(10,2), rate DECIMAL(4,3))
RETURNS DECIMAL(10,2) DETERMINISTIC          -- honest
RETURN price * (1 + rate);
 
CREATE FUNCTION age_days(d DATE)
RETURNS INT NOT DETERMINISTIC                 -- honest
RETURN DATEDIFF(NOW(), d);

Case G — procedure: atomic transaction, crash midway

Steps.

1. Normal path: both UPDATEs run, then COMMIT. Why COMMIT matters? It makes both changes permanent together. $300-100=200$ and $50+100=150$.
2. Crash before COMMIT: the transaction is not committed, so on restart the DB rolls back everything since START TRANSACTION (see Transactions and ACID). Why? Atomicity: all-or-nothing. Uncommitted work is discarded, not left half-done.
3. Therefore after crash: account 1 is still $300$, account 2 still $50$. No money vanished. Why this is the whole point of a procedure? The atomic unit lives server-side, not in fragile app code that could crash between two separate network calls.

Case H — procedure returning via OUT (no RETURN statement)

Steps.

1. Declare an OUT parameter. Why not RETURN? A procedure has no RETURNS type; it hands values back by writing into OUT/INOUT parameters, which the caller then reads.
2. Compute into it.

DELIMITER //
CREATE PROCEDURE count_active(OUT n INT)
BEGIN
  SELECT COUNT(*) INTO n FROM users WHERE active = 1;
END //
DELIMITER //
 
CALL count_active(@result);
SELECT @result;   -- 28

3. 40 - 12 = 28 active users; the procedure sets n = 28, the caller reads @result = 28. Why this shows the proc/function line? You cannot do SELECT count_active() — a procedure is not usable inside an expression; it is CALLed and communicates through @result.

Case I — word problem (real world): loyalty points

Steps.

1. "Every time a row is inserted... automatically" → this screams trigger (no one should have to remember to call it). Timing AFTER INSERT: we react once the purchase is final and write to a different table (users). Why AFTER not BEFORE? We are not changing the purchase row; we are updating users. Reacting = AFTER.
2. Read NEW.amount (INSERT → only NEW).
3. Points $=\lfloor 350/100\rfloor =\lfloor 3.5\rfloor =3$.

DELIMITER //
CREATE TRIGGER award_points
AFTER INSERT ON purchases
FOR EACH ROW
  UPDATE users SET points = points + FLOOR(NEW.amount / 100)
  WHERE id = NEW.user_id;
DELIMITER //

Case J — exam twist: the cascading trigger surprise

Steps.

1. One INSERT into orders fires T1 once → 1 row into order_events. Why? FOR EACH ROW, one order row = one firing.
2. That insert into order_events itself fires T2 → 1 row into event_audit. Why this is the twist? A trigger's action can trigger another trigger. This is a cascade — invisible chained control flow. The parent note's mistake "triggers are free magic" warns about exactly this: "spooky action at a distance."
3. Net: 1 row in event_audit, but produced by a chain nobody explicitly called. Why it's dangerous? Deep cascades hurt performance, are hard to debug, and can even loop. Keep triggers for invariants, not general logic.

Full-matrix recall

Connections

• 4.4.12 Stored procedures, triggers, functions (Hinglish) — the parent topic these examples drill.
• Transactions and ACID — Ex 7's rollback is atomicity in action.
• SQL Queries (SELECT, JOIN) — Ex 6's function plugs into a SELECT.
• Database Normalization — Ex 9's trigger maintains a derived column (points).
• Indexes — Ex 6's deterministic function can back a functional index.
• Concurrency Control — all these triggers/procs run under locks.