Stored procedures, triggers, functions
1. Stored Procedures
WHY do they exist? Imagine transferring money: you must (1) subtract from account A, (2) add to account B. If your app does this in two separate queries and crashes between them, money vanishes. A stored procedure wraps both in one server-side call so the logic — and its transaction — lives in one trusted place.
HOW it looks (MySQL syntax):
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 //
-- call it:
CALL transfer(1, 2, 100.00);2. Functions (User-Defined Functions, UDFs)
WHY separate from procedures? Because the query planner needs to call it per row inside a query. A SELECT discount(price) FROM items would make no sense if discount could COMMIT a transaction or return nothing.
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 //
-- use inside a query:
SELECT name, net_price(price, 0.18) AS with_tax FROM products;| Stored Procedure | Function | |
|---|---|---|
| Invoked by | CALL (explicitly) |
inside SQL expressions |
| Returns | optional (OUT params) | exactly one value |
| Can modify data | yes | usually no / restricted |
| Transactions | yes | typically no |
Use in SELECT |
no | yes |
3. Triggers
WHY triggers? To enforce rules no matter who or what touches the table — even a careless admin running raw SQL. Common uses: audit logs, auto-maintaining derived columns, validation, cascading updates.
The magic pseudo-tables: OLD and NEW
NEW.col→ the incoming value (available in INSERT and UPDATE).OLD.col→ the previous value (available in UPDATE and DELETE).
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 //
Common Mistakes (Steel-manned)
Recall Feynman: explain it to a 12-year-old
Think of the database as a toy box. Normally you reach in and rearrange toys.
- A stored procedure is like a button labeled "Tidy Up" — you press it and it does a bunch of steps for you.
- A function is like a little calculator inside the box — you give it numbers and it hands one answer back, so you can use it while doing other math.
- A trigger is like a magic sensor: whenever you add or remove a toy, it automatically writes a note in a diary — you never asked it to, it just always does.
Connections
- Transactions and ACID — procedures wrap multi-statement atomic units.
- SQL Queries (SELECT, JOIN) — functions plug into these expressions.
- Database Normalization — triggers can maintain derived/denormalized columns.
- Indexes — deterministic functions can power functional indexes.
- Concurrency Control — triggers and procedures run under locks.
What invokes a stored procedure?
CALL procName(args) statement.What must a function always do that a procedure need not?
RETURNS type + RETURN).Can you use a stored procedure inside a SELECT clause?
What are the two trigger timings?
BEFORE and AFTER the data event.In which trigger events is OLD available?
In which trigger events is NEW available?
Why use BEFORE instead of AFTER in a trigger?
NEW row before it is written.What does FOR EACH ROW mean?
Why does DELIMITER // appear around procedure definitions?
; aren't read as the procedure's end.What does declaring a function DETERMINISTIC promise?
What does an INOUT parameter do?
Why prefer a stored procedure for a money transfer over app-side logic?
Can a DELETE trigger reference NEW?
OLD exists.Concept Map
Hinglish (regional understanding)
Intuition Hinglish mein samjho
Dekho, normally hum sochte hain ki database sirf data store karta hai aur hum app se queries bhejte hain. Lekin database khud bhi code chala sakta hai. Iske teen roop hain. Stored procedure ek button jaisa hai — aap CALL karke use chalate ho, ismein multiple steps aur transactions ho sakte hain (jaise paisa transfer karna: ek account se minus, doosre mein plus — dono ek saath ya bilkul nahi). Function ek chhota calculator hai jo ek value return karta hai aur use aap seedhe SELECT ke andar laga sakte ho, jaise SELECT net_price(price, 0.18).
Trigger sabse interesting hai — yeh apne aap chalta hai jab table mein koi INSERT, UPDATE ya DELETE hota hai. Aapko CALL karne ki zaroorat nahi, woh khud trigger ho jaata hai. Iska sabse bada faayda yeh hai ki koi bhi banda data badle — chahe app ho ya koi admin raw SQL chala raha ho — rule automatically lag jaata hai. Audit log banana, validation karna, ya derived column maintain karna iske common use hain.
Yaad rakhne wali ek key baat: trigger ke andar OLD aur NEW naam ki pseudo-rows hoti hain. NEW matlab aane wali nayi value (INSERT aur UPDATE mein), OLD matlab purani value (UPDATE aur DELETE mein). Toh DELETE mein sirf OLD milega, INSERT mein sirf NEW, aur UPDATE mein dono. BEFORE trigger se aap incoming value ko badal sakte ho, AFTER se sirf react kar sakte ho (jaise log likhna).
Yeh sab kyun matter karta hai? Kyunki logic ko data ke paas rakhne se network trips kam hote hain, business rules ek jagah centralized rehte hain, aur consistency database khud guarantee karta hai — app par bharosa nahi karna padta. Bas dhyaan rakho ki trigger overuse mat karo, kyunki woh chhupe hue chalte hain aur debugging mushkil ho jaati hai.