Stored procedures, triggers, functions

4.4.12 · Coding › Databases

1. Stored Procedures

WHY yeh exist karte hain? Paisa transfer karne ki imagine karo: tumhe (1) account A se subtract karna hai, (2) account B mein add karna hai. Agar tumhari app yeh do alag queries mein karti hai aur beech mein crash ho jaaye, toh paisa gayab ho jaata hai. Ek stored procedure dono ko ek server-side call mein wrap karta hai taaki logic — aur uska transaction — ek trusted jagah mein rahe.

HOW yeh dikhta hai (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 procedures se alag? Kyunki query planner ko ise query ke andar har row ke liye call karna hota hai. SELECT discount(price) FROM items ka koi matlab nahi hota agar discount koi COMMIT transaction kar sake ya kuch return na kare.

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;

3. Triggers

WHY triggers? Rules enforce karne ke liye chahe table ko koi bhi ya kuch bhi touch kare — even ek careless admin jo raw SQL run kar raha ho. Common uses: audit logs, derived columns auto-maintain karna, validation, cascading updates.

Magic pseudo-tables: OLD aur NEW

• NEW.col → incoming value (INSERT aur UPDATE mein available).
• OLD.col → previous value (UPDATE aur DELETE mein available).

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)

Connections

• Transactions and ACID — procedures multi-statement atomic units wrap karte hain.
• SQL Queries (SELECT, JOIN) — functions in expressions mein plug hote hain.
• Database Normalization — triggers derived/denormalized columns maintain kar sakte hain.
• Indexes — deterministic functions functional indexes power kar sakte hain.
• Concurrency Control — triggers aur procedures locks ke under run karte hain.

Concept Map