Exercises — Stored procedures, triggers, functions

1. Trigger — the defining trait is automatic firing on a data-change event; nobody calls it.
2. Stored procedure — the word CALL is the giveaway; it is invoked explicitly and need not return a value.
3. Function — it returns exactly one value and is embedded inside an SQL expression.

• AFTER INSERT → only NEW (a fresh row appeared; there is no previous version).
• BEFORE DELETE → only OLD (a row is about to vanish; there is no incoming version).
• AFTER UPDATE → both OLD and NEW (the row changed from one value to another).

Mnemonic from the parent: OLD–DELETE, NEW–INSERT, UPDATE = BOTH.

DELIMITER //
CREATE FUNCTION net_price(price DECIMAL(10,2), rate DECIMAL(4,3))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * (1 + rate);
END //
DELIMITER ;

Why RETURNS? The engine must know the output type to slot the result into an expression. Why DETERMINISTIC? Same inputs always give the same output (no NOW(), no table reads), so it is honestly a pure calculator.

By hand: $200.00\times (1+0.18)=200\times 1.18=236.00$.

DELIMITER //
CREATE PROCEDURE transfer(IN src INT, IN dst INT, IN amt DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amt WHERE id = src;
  UPDATE accounts SET balance = balance + amt WHERE id = dst;
  COMMIT;
END //
DELIMITER ;

Why the transaction? Either both updates land or neither does — see Transactions and ACID. If the server crashed after the first UPDATE, the missing COMMIT means the whole thing rolls back; money is never lost.

Trace:

• Start: acc1 = 500.00, acc2 = 300.00.
• balance - 120 on acc1 → 380.00.
• balance + 120 on acc2 → 420.00.
• Total before = 800.00, total after = 380 + 420 = 800.00 — conserved, as it must be.

transfer is a procedure, not a function. A SELECT evaluates an expression per row and needs each call to return one value with no side effects. But transfer:

• returns nothing (no RETURNS),
• runs START TRANSACTION ... COMMIT (a side effect the query planner must never trigger mid-scan).

So the engine refuses. Procedures are invoked with CALL transfer(1,2,100.00); standalone. The invocation context — inside an expression vs. standalone CALL — is the dividing line, exactly as the parent note's first Common Mistake warns.

DETERMINISTIC is a promise: "same inputs → same output, forever." The optimizer trusts it and may cache a computed value or reuse it across rows/queries. But NOW() changes with time, so the true output depends on more than dob.

Concrete failure: the DB caches age_now('2000-01-01') = 25 and keeps serving 25 even after a year passes, or uses it to build a functional index (see Indexes) that silently goes stale. Result: silently wrong data with no error message. The fix is to declare it NOT DETERMINISTIC (and READS SQL DATA where relevant), or better, pass the reference date in as a parameter so it becomes honestly pure.

DELIMITER //
CREATE TRIGGER audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO salary_log(emp_id, old_sal, new_sal, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
  END IF;
END //
DELIMITER ;

Why AFTER? We only log a change once it is safely written — we are reacting, not editing. Why FOR EACH ROW? It fires once per affected row, so both OLD and NEW refer to that one row. Why the IF? To skip noise when the salary was untouched.

The UPDATE hits 3 rows, so the trigger body runs 3 times. But in each, NEW.salary = OLD.salary (only dept changed), so the guard is false every time. Zero log rows are inserted.

It must live in a BEFORE UPDATE trigger, because a procedure only guards its own code path — a careless admin running a raw UPDATE bypasses it. A trigger fires no matter who touches the table.

DELIMITER //
CREATE TRIGGER no_negative
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Balance cannot go negative';
  END IF;
END //
DELIMITER ;

Why BEFORE? We must inspect (and reject) the incoming value before it is written — NEW.balance is still editable/inspectable here. In an AFTER trigger the bad value would already be committed. Why SIGNAL? It raises an error, which forces the surrounding transaction (from transfer) to roll back — tying the invariant into Transactions and ACID.

Test: transfer(1, 2, 999999.00) when acc1 has only 500 → the first UPDATE sets NEW.balance = 500 - 999999 = -999499, the trigger fires, SIGNAL aborts, the transaction rolls back, nothing changes.

FOR EACH ROW triggers fire once per affected row, and inserts cascade:

• Into A: 4 rows (your statement).
• A's trigger fires 4 times → 4 rows into B.
• B's trigger fires 4 times → 4 rows into C.

Total = $4+4+4=12$ rows. This is exactly the "spooky action at a distance" the parent note warns about: one visible statement quietly produced twelve writes through hidden chained triggers.

This is a lost update. Both sessions read 500, both write 400 — but two transfers of 100 should have left 300. One update silently overwrote the other; 100 vanished.

What saves us: the UPDATE accounts SET balance = balance - amt form does not read-then-write in the app; it computes inside the engine under a row lock. Whichever transaction grabs the lock on account 1 first applies -100, releases at COMMIT, then the second reads the already-updated 400 and applies -100 → 300. This locking is the subject of Concurrency Control. The dangerous version is when logic reads the balance into a variable in the procedure and writes it back — that reintroduces the race.

Timeline inside transfer:

1. START TRANSACTION.
2. UPDATE src → the BEFORE guard runs; if the debit is legal it is applied within the transaction (not yet committed).
3. UPDATE dst fails → an error is raised.
4. Because we never reach COMMIT, the transaction is rolled back, and step 2's debit is undone.

So no — the debit is not left behind. Atomicity (Transactions and ACID) means all statements between START TRANSACTION and COMMIT are one indivisible unit: reaching an error before COMMIT reverts everything, including the earlier successful UPDATE. This is the whole reason the money-transfer logic lives server-side in one transactional block.