Stored procedures, triggers, functions: Question bank

True or false — justify

Say TRUE or FALSE first, then give the reason — a bare verdict scores nothing.

A function and a stored procedure are interchangeable; call whichever you like.

FALSE. A function returns exactly one value and is meant for inside a query (SELECT tax(p)); a procedure is invoked standalone with CALL and may have side effects/transactions. The invocation context separates them.

SELECT my_procedure() is valid SQL.

FALSE. Procedures are not expressions — they cannot sit inside a SELECT. You must write CALL my_procedure(...) as its own statement.

Marking a function DETERMINISTIC makes it run faster by itself.

FALSE. It is a promise ("same inputs → same output"), not a speed knob. It merely lets the optimizer cache/reuse results; if the promise is false the cache serves stale, wrong data.

A trigger can be invoked manually with CALL audit_salary().

FALSE. Triggers fire automatically on INSERT/UPDATE/DELETE events only — there is no manual entry point for them.

In an AFTER UPDATE trigger you can still change the incoming row with NEW.col = ....

FALSE. AFTER means the row is already written; NEW is read-only there. Only a BEFORE trigger may assign to NEW.

A function may safely COMMIT a transaction.

FALSE (in general). Functions are meant to be side-effect-free and run per-row inside a query; committing mid-query would be nonsensical, so most engines forbid transaction control in functions.

FOR EACH ROW means the trigger fires once no matter how many rows change.

FALSE. It fires once per affected row. An UPDATE hitting 50 rows runs the trigger body 50 times.

Putting logic in a stored procedure removes the need for transactions.

FALSE. The procedure is exactly where you place START TRANSACTION ... COMMIT; it wraps atomicity server-side rather than replacing it.

A stored procedure must return a value like a function does.

FALSE. Procedures return nothing by default; they may pass data back only through OUT/INOUT parameters, and often return nothing at all.

Spot the error

Each line describes buggy code or reasoning. Say what's wrong and how to fix it.

"In my DELETE trigger I read NEW.id to see what was removed."

On DELETE there is no new row — only OLD exists. Use OLD.id. Mnemonic: DELETE keeps the OLD, INSERT brings the NEW, UPDATE has both.

"My function reads NOW() inside but I marked it DETERMINISTIC for speed."

NOW() changes every call, so the function is not deterministic. Lying lets the optimizer cache one timestamp → silently stale results. Remove DETERMINISTIC.

"I wrote a procedure body but omitted DELIMITER //, and the DB errored at the first inner ;."

Without changing the delimiter, the engine thinks the procedure ends at the first internal ;. Wrap the definition in DELIMITER // ... // so the whole body is treated as one statement.

"My function has an IF branch that returns nothing when the condition is false."

A function must yield a value on every path. A branch with no RETURN is illegal; add a RETURN (or an ELSE) covering the missing case.

"I used a BEFORE INSERT trigger to log the row into an audit table, then found duplicate logs when the insert failed."

BEFORE fires even for inserts that later fail a constraint. For logging committed changes use AFTER, which runs only once the row is actually written.

"I declared IN result INT to hand a computed answer back to the caller."

IN is read-only inside the procedure — the caller can't see writes to it. Use OUT (write-only back) or INOUT (both directions).

"I put all my business logic across five chained triggers and now can't tell why a value keeps changing."

Triggers are hidden control flow that can cascade into one another — "spooky action at a distance." Reserve them for invariants; move general logic into explicit procedures you CALL.

Why questions

Give the reason, not just a restatement.

Why does a function need RETURNS type but a procedure does not?

The query planner plugs the function's output into an expression, so it must know the value's type in advance. A procedure isn't part of an expression, so no return type is required.

Why prefer AFTER over BEFORE for an audit-log trigger?

You want to log a change only once it is final/committed to the row. BEFORE runs before the write is settled and could log changes that never actually persist.

Why does putting a transfer in a stored procedure prevent money vanishing?

The two updates plus START TRANSACTION ... COMMIT live in one server-side call, so either both happen or neither — the app can't crash between two separate network queries. See Transactions and ACID.

Why can't a procedure appear inside SELECT discount(price) FROM items?

The planner calls that routine per row and expects a single value with no side effects; a procedure may commit or return nothing, which is meaningless mid-query. Only functions fit there.

Why does DETERMINISTIC let a function power a functional index?

An index stores precomputed outputs; that is only safe if the same inputs always give the same output. Non-deterministic functions would make the stored index values wrong.

Why fire triggers instead of validating only in the application?

A trigger enforces the rule no matter who touches the table — even a raw admin query bypassing the app. Application checks can be skipped; the DB-level trigger cannot.

Why does an UPDATE trigger give access to both OLD and NEW?

An update replaces an existing row with new data, so both the previous value (OLD) and the incoming value (NEW) meaningfully exist — you can compare them.

Edge cases

Boundary and degenerate scenarios.

An UPDATE sets salary = salary (no real change) — does the AFTER UPDATE trigger still fire?

The trigger fires for the row, but a guard like IF NEW.salary <> OLD.salary skips logging when nothing actually changed — that's why the guard exists (avoids noise).

An UPDATE ... WHERE matches zero rows — how many times does a FOR EACH ROW trigger run?

Zero times. With no affected rows there is no "each row," so the body never executes.

In a BEFORE INSERT trigger, is OLD available?

No. A pure INSERT has no previous row, so only NEW exists; referencing OLD is an error.

Two triggers both AFTER UPDATE on the same table — what determines order, and what's the risk?

They run in a defined order (declaration/FOLLOWS/PRECEDES order per engine), and they can chain unexpectedly — one trigger's write may fire another, causing cascades that are hard to trace.

A function is declared DETERMINISTIC but internally reads a lookup table that changes over time — safe?

No. Reading table data makes the output depend on more than its inputs; it is effectively non-deterministic and may be cached to a stale value. It should not be marked DETERMINISTIC.

A stored procedure omits COMMIT but does two UPDATEs — what happens?

Behavior depends on autocommit/session settings; without an explicit commit the changes may stay uncommitted or roll back on error, breaking the intended atomicity. Always manage the transaction explicitly.

Under Concurrency Control, two callers run the transfer procedure on the same account at once — is it automatically safe?

Not by itself. The procedure runs under the engine's locks/isolation; correctness depends on proper isolation and locking, not merely on the code being server-side.