Stored Procedures, Functions & Triggers

Stored procedure, function ও trigger — logic যা database-এর ভেতরেই থাকে

Read: ~35 min Hard 12 practice problems Live SQL runner

1. Where Should Business Logic Live?

Every real application has rules: "a customer cannot place an order if their wallet balance is negative", "every time a price changes, write to the audit log", "when a student is deleted, also delete their grades". These rules are business logic. The hard question is: where do they live — in your application code (Node.js, Django, Laravel) or inside the database itself?

প্রতিটি বাস্তব অ্যাপ্লিকেশনে কিছু নিয়ম থাকে: "wallet-এ টাকা না থাকলে order দেওয়া যাবে না", "price পরিবর্তন হলেই audit log-এ লিখতে হবে", "student delete হলে তার সব grade-ও delete হবে"। এই নিয়মগুলোকে বলে business logic। প্রশ্ন হলো — এই logic কোথায় রাখব? অ্যাপ্লিকেশন কোডে (Node.js, Django, Laravel)? নাকি database-এর ভেতরে?

The database gives you three tools to keep logic close to the data: stored procedures, user-defined functions (UDFs), and triggers. This module shows what each one is, when to use it, and — just as important — when not to.

Quick map of this module
Stored procedure = a named block of SQL the app explicitly calls.
Function = like a procedure, but returns a value and can be used inside SELECT.
Trigger = code that runs automatically when a row is inserted, updated, or deleted.

2. App-side vs DB-side Logic — The Trade-off

Putting logic in the database centralises it: every app, every script, every analyst sees the same rule enforced. Putting logic in the app keeps it visible to your team — your VCS, your tests, your code review. Both have a place; the wrong choice ruins maintainability.

Database-এর ভেতরে logic রাখলে সব app একই নিয়ম মানতে বাধ্য হয় — এটা শক্তিশালী। কিন্তু সমস্যা হলো এই logic আপনার Git repo-তে থাকে না, code review-এ আসে না, এবং debug কঠিন। App-side-এ রাখলে দেখা যায়, test হয়, কিন্তু একই rule পাঁচটা service-এ আলাদা আলাদা ভাবে লেখা হতে পারে।

✅ Put logic in the DB when… (কখন DB-তে রাখবেন)

It is a data integrity rule (no negative balance, unique email).
Multiple apps / scripts / analysts touch the same table.
You need an audit trail that cannot be bypassed.
The work is set-based and would round-trip thousands of rows otherwise.

⚠️ Keep logic in the app when… (কখন app-এ রাখবেন)

It involves external systems (payment gateway, email, SMS).
It changes frequently or differs per client / region.
It needs unit tests, mocks, code review.
You may switch databases later — portability matters.

Figure 21.1 — Same rule, two homes. The DB-side version is harder to bypass.

3. Stored Procedures — A Named Block of SQL

A stored procedure is a chunk of SQL (often with control-flow like IF, LOOP) saved inside the database under a name. The application calls it with CALL transfer_money(123, 456, 500) instead of sending five separate statements. Procedures are great for multi-step transactions that must always run together.

Stored procedure মানে database-এর ভেতরে নাম দিয়ে রাখা একটি SQL ব্লক। App প্রতিবার লম্বা SQL না পাঠিয়ে শুধু CALL transfer_money(...) বললেই কাজ হয়ে যায়। কয়েকটি ধাপ একসাথে চালাতে হলে — যেমন একজনের wallet থেকে টাকা কেটে আরেকজনের wallet-এ যোগ করা — procedure খুব কাজে আসে।

SQLite note (গুরুত্বপূর্ণ)
SQLite-এ pure SQL দিয়ে stored procedure বা user-defined function লেখা যায় না — SQLite এই দুটি feature support করে না (you can register UDFs from C/Python/JS host code, but not from SQL itself). তাই নিচের stored-procedure এবং function উদাহরণগুলো PostgreSQL-এর PL/pgSQL syntax-এ দেওয়া — এগুলো SQLite-এ চলবে না, conceptual তুলনার জন্য রাখা হয়েছে। এই module-এ শুধুমাত্র trigger উদাহরণগুলো SQLite-এ লাইভ চলবে।

Postgres-style stored procedure (this is Postgres, not SQLite)

transfer.sql · PostgreSQL — NOT runnable here

-- PostgreSQL syntax. Runs in psql / pgAdmin, NOT in SQLite.
CREATE OR REPLACE PROCEDURE transfer_money(
    sender_id   INT,
    receiver_id INT,
    amount      NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    sender_balance NUMERIC;
BEGIN
    SELECT balance INTO sender_balance
    FROM wallets WHERE user_id = sender_id FOR UPDATE;

    IF sender_balance < amount THEN
        RAISE EXCEPTION 'Insufficient balance';
    END IF;

    UPDATE wallets SET balance = balance - amount WHERE user_id = sender_id;
    UPDATE wallets SET balance = balance + amount WHERE user_id = receiver_id;

    INSERT INTO transfer_log(sender, receiver, amount, at)
    VALUES (sender_id, receiver_id, amount, NOW());
END;
$$;

-- Application calls it like this:
-- CALL transfer_money(101, 202, 500);

Notice how the entire money-transfer is one atomic operation — balance check, two updates, and an audit insert — and the app cannot accidentally skip the audit step. That is the defining strength of stored procedures.

4. User-Defined Functions (UDFs)

A function is like a procedure, but it returns a value and can be used directly inside a query — for example SELECT student_name, gpa_letter(gpa) FROM students;. Functions are usually pure: same input always gives the same output, no side-effects.

Function-ও procedure-এর মতোই — কিন্তু এটি একটি মান return করে এবং সরাসরি SELECT-এর ভেতরে ব্যবহার করা যায়। যেমন GPA → letter grade convert করার একটি function বানালে আপনি প্রতিটি query-তে সেটি পুনরায় ব্যবহার করতে পারবেন। Function সাধারণত pure হয় — একই input-এ সবসময় একই output, কোনো side-effect নেই।

Postgres-style function (this is Postgres, not SQLite)

gpa_letter.sql · PostgreSQL — NOT runnable here

-- PostgreSQL. SQLite cannot define functions in pure SQL.
CREATE OR REPLACE FUNCTION gpa_letter(g NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF g >= 3.75 THEN RETURN 'A+';
    ELSIF g >= 3.50 THEN RETURN 'A';
    ELSIF g >= 3.00 THEN RETURN 'A-';
    ELSIF g >= 2.50 THEN RETURN 'B';
    ELSE                     RETURN 'C';
    END IF;
END;
$$;

-- Use it just like any built-in function:
SELECT name, gpa, gpa_letter(gpa) AS grade
FROM students;

The same logic, runnable in SQLite using CASE

Since SQLite cannot define a function, the idiomatic SQLite version is to write the logic inline using CASE. It is less reusable but works everywhere.

gpa_letter_sqlite.sql

SELECT
    name,
    gpa,
    CASE
        WHEN gpa >= 3.75 THEN 'A+'
        WHEN gpa >= 3.50 THEN 'A'
        WHEN gpa >= 3.00 THEN 'A-'
        WHEN gpa >= 2.50 THEN 'B'
        ELSE 'C'
    END AS grade
FROM students
ORDER BY gpa DESC;

5. Triggers — Code That Runs Automatically

A trigger is a piece of SQL the database runs by itself whenever a particular event (INSERT, UPDATE, DELETE) happens on a particular table. The app does not call it — the database does. Triggers are the right answer for things you want to be impossible to forget, like writing to an audit log.

Trigger হলো এমন একটি SQL ব্লক, যেটি database নিজে থেকেই চালায় — যখনই কোনো নির্দিষ্ট table-এ INSERT, UPDATE বা DELETE হয়। App-কে এটি call করতে হয় না। Audit log লেখা, derived column update করা, বা একটি নিয়ম জোর করে enforce করার জন্য trigger অসাধারণ।

Timing & Granularity

Timing	When it fires	Typical use
`BEFORE INSERT`	Just before the row is inserted	Validate, clean, set defaults, reject
`AFTER INSERT`	Just after the row is inserted	Audit log, send notification, update cache row
`BEFORE UPDATE`	Before the new values overwrite the old	Block illegal changes, clean inputs
`AFTER UPDATE`	After the change is committed	Audit log "old → new", denormalised counters
`BEFORE DELETE`	Before a row is removed	Reject if referenced, archive into history table
`AFTER DELETE`	After the row is gone	Cascade-style cleanup, log who/when

Triggers also have a granularity: FOR EACH ROW (fires once per affected row, the common case) or FOR EACH STATEMENT (fires once for the whole statement, regardless of row count). SQLite supports only FOR EACH ROW; PostgreSQL supports both.

একটি UPDATE যদি ১০০টি row পরিবর্তন করে, তাহলে FOR EACH ROW trigger ১০০ বার চলবে, আর FOR EACH STATEMENT trigger মাত্র ১ বার চলবে। SQLite শুধু FOR EACH ROW support করে — তাই আমাদের সব উদাহরণ সেই pattern অনুসরণ করবে।

6. Live Trigger #1 — Block Negative bKash Wallets

The simplest, most useful trigger pattern: stop bad data from entering the table. We model a tiny bKash-style wallets table and add a trigger that raises an error if any UPDATE would push a balance below zero. This is enforced no matter which app touches the row.

নিচে আমরা একটি ছোট bKash-style wallets table বানাবো এবং একটি trigger যোগ করবো — যেটি UPDATE চেষ্টা করলে balance ০-এর নিচে যাচ্ছে কিনা চেক করবে। যদি যায়, তাহলে error দিয়ে transaction বাতিল করে দেবে। App-side validation ভুলে গেলেও database আপনাকে বাঁচাবে।

no_negative_balance.sql

-- Try to overdraw Nusrat's wallet (balance = 300, attempt -500):
UPDATE wallets SET balance = balance - 500
WHERE user_id = 202;

-- The trigger should ABORT this. The wallets table stays unchanged:
SELECT * FROM wallets;

Look closely at the trigger body: RAISE(ABORT, '...') stops the statement and rolls it back. NEW is a virtual row containing the values that would be written — we inspect it before the row hits disk. OLD would refer to the values being replaced.

7. Live Trigger #2 — The Audit-Log Pattern

This is the single most popular real-world use of triggers. Every time someone changes a price in a Daraz-style product table, we log the change — who, when, old value, new value — into an audit_log table that nobody can bypass.

Daraz-এর মতো কোনো e-commerce database-এ price পরিবর্তন হলে আমরা চাই সেটার একটি record থাকুক — কখন পরিবর্তন হলো, পুরাতন দাম কত ছিল, নতুন কত হলো। এই কাজটি app-এ লিখতে গেলে কোনো একটি service ভুলে যেতে পারে; trigger দিয়ে লিখলে কেউ আর ভুলতে পারবে না।

price_audit.sql

-- Two price changes happen (a discount, then a correction):
UPDATE products SET price = 42000 WHERE id = 1;
UPDATE products SET price = 8900  WHERE id = 2;

-- An "update" that does not actually change the price — trigger should NOT fire:
UPDATE products SET price = 8900  WHERE id = 2;

-- The audit table should have exactly TWO rows:
SELECT * FROM price_audit;

Two details to notice: the WHEN OLD.price <> NEW.price clause prevents the trigger from logging "no-op" updates. And the audit row is written by the database engine itself — the application does not even know the table exists.

8. The Trigger-Heavy Trap

Triggers are powerful — and that is the danger. A senior developer once described a trigger-heavy database as "a haunted house: every UPDATE sets off ten ghosts you cannot see." Below are the real-world failure modes to keep in mind.

Trigger অনেক শক্তিশালী, এবং সেখানেই বিপদ। অভিজ্ঞ ডেভেলপাররা trigger-ভর্তি database-কে বলে "ভূতের বাড়ি" — প্রতিটি UPDATE চালালে অদৃশ্য কোড চলে যায়। নিচে কিছু সাধারণ সমস্যা দেওয়া হলো — trigger ব্যবহারের আগে এগুলো সবসময় মাথায় রাখবেন।

✅ Trigger does well (ভালো ব্যবহার)

Audit logs / change history
Maintaining a derived counter
Hard data-integrity rules
Soft-delete bookkeeping (deleted_at)

⚠️ Trigger does badly (সমস্যা)

Calling external systems (HTTP, email)
Heavy computation — slows every write
Triggers that fire other triggers (cascade chains)
Hidden behaviour that surprises new devs

Rule of thumb (নিয়ম)
If a junior dev cannot predict, by reading the table definition, what an UPDATE will do, you have too much trigger logic. Document every trigger and keep them small.

9. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Stored procedure	Named SQL block, called explicitly with `CALL`.	App যেটি `CALL` দিয়ে চালায় — DB-তে নাম দিয়ে রাখা SQL ব্লক।
Function (UDF)	Returns a value, usable inside `SELECT`.	মান return করে এবং `SELECT`-এর ভেতরে ব্যবহার করা যায়।
Trigger	Auto-runs on INSERT/UPDATE/DELETE.	INSERT/UPDATE/DELETE হলে নিজে থেকেই চলে।
`NEW` / `OLD`	Virtual rows in a row-level trigger body.	Trigger-এর ভেতরে নতুন এবং পুরাতন row।
PL/pgSQL	Postgres' procedural language extension to SQL.	PostgreSQL-এর procedure language।
FOR EACH ROW	Trigger fires once per affected row.	প্রতি affected row-এর জন্য একবার চলে।
RAISE(ABORT, …)	SQLite construct that fails & rolls back the statement.	SQLite-এ statement বাতিল করে rollback করায়।

10. Practice Problems

Try each problem yourself, then expand the answer to run it. Most answers are full SQLite triggers you can run live; a few are conceptual since SQLite cannot define procedures or functions.

প্রতিটি সমস্যা প্রথমে নিজে চেষ্টা করুন। SQLite-এ trigger-এর উত্তরগুলো লাইভ চলবে। Procedure / function সংক্রান্ত প্রশ্নগুলোর উত্তর Postgres-style — শুধু concept-এর জন্য।

Write a SQLite trigger that prevents anyone from inserting a student with age < 5.

এমন একটি SQLite trigger লিখুন যা age < 5 হলে student insert হতে দেবে না।
✨ Show Answer (উত্তর দেখুন)
ans1.sql
```
INSERT INTO students(name,age) VALUES('Sumi', 12);  -- ok
INSERT INTO students(name,age) VALUES('Tiny', 3);  -- aborted
SELECT * FROM students;
```
Write an AFTER INSERT trigger on a library_loans table that records every new loan into a loan_audit table.

প্রতিটি নতুন বই-ধার record করে loan_audit-এ লিখবে — এমন একটি trigger লিখুন।
✨ Show Answer
ans2.sql
```
INSERT INTO library_loans(member,book) VALUES('Rahim','Padma Nadir Majhi');
INSERT INTO library_loans(member,book) VALUES('Sumi', 'Pather Panchali');
SELECT member, book FROM loan_audit;
```
Write a BEFORE UPDATE trigger that prevents the email column of the users table from ever becoming NULL.

users.email কখনো NULL না হতে দেয় — এমন trigger লিখুন।
✨ Show Answer
ans3.sql
```
UPDATE users SET email = NULL WHERE id = 1;  -- aborted
SELECT * FROM users;
```
In one paragraph, explain when you would put logic in the database vs in the application.

এক অনুচ্ছেদে ব্যাখ্যা করুন — কখন database-এ logic রাখবেন, কখন app-এ।

✨ Show Answer

Answer: Put logic in the database when it is a hard data-integrity rule that must hold no matter which app touches the data (uniqueness, non-negative balances, audit trails). Keep logic in the application when it changes often, depends on external services (HTTP, email, SMS), needs unit tests, or differs by client/region. The DB enforces the few absolute rules; the app encodes the changing business rules.

Database-এ logic রাখবেন তখনই, যখন সেটা একটি অলঙ্ঘনীয় integrity rule (যেমন uniqueness, balance ≥ 0, audit log)। আর app-এ রাখবেন যেগুলো ঘন ঘন বদলায়, বাইরের system-এ call করে, test দরকার, বা client-ভেদে আলাদা। DB-তে গুটিকয়েক হার্ড-rule, app-এ business logic — এটিই সঠিক ভাগ।
Write a trigger that automatically fills a created_at column with the current timestamp on insert (without using DEFAULT).

DEFAULT ব্যবহার না করে, trigger দিয়ে insert-এর সময় created_at auto-fill করুন।
✨ Show Answer
ans5.sql
```
INSERT INTO notes(body) VALUES('Hello!');
INSERT INTO notes(body) VALUES('Bangladesh');
SELECT * FROM notes;
```
Write a trigger that copies any deleted student into a students_archive table before they are removed.

Student delete হবার আগে students_archive-এ কপি হবে — trigger লিখুন।
✨ Show Answer
ans6.sql
```
DELETE FROM students WHERE id = 2;
SELECT * FROM students_archive;
```
Maintain a denormalised order_count column in a customers table by using triggers on orders.

orders table-এ trigger বসিয়ে customers.order_count auto-update করুন।
✨ Show Answer
ans7.sql
```
INSERT INTO orders(customer_id) VALUES(1),(1),(2);
SELECT * FROM customers;
DELETE FROM orders WHERE id = 1;
SELECT * FROM customers;
```
List three reasons a trigger-heavy database can be hard to maintain.

Trigger-heavy database রক্ষণাবেক্ষণ কঠিন কেন — তিনটি কারণ লিখুন।

✨ Show Answer

Answer: (1) Hidden behaviour — a developer reading UPDATE sees no clue that ten other tables also got modified. (2) Cascade chains — one trigger fires another, which fires another; debugging is painful. (3) No version control — trigger SQL often does not live in the app's git repo, so changes happen invisibly in production.

(১) লুকানো আচরণ — কোডে শুধু UPDATE দেখা যায়, কিন্তু ভিতরে আরও দশটি table পরিবর্তন হচ্ছে। (২) Cascade chain — একটি trigger আরেকটি call করে, ডিবাগ করা কঠিন। (৩) Version control নেই — trigger কোড সাধারণত git-এ থাকে না।
Convert this conceptual stored-procedure idea — "deduct stock when an order is inserted" — into a runnable SQLite trigger.

Order insert হলে product-এর stock কমিয়ে দেবে — এটি SQLite trigger-এ লিখুন।
✨ Show Answer
ans9.sql
```
INSERT INTO orders(product_id,qty) VALUES(1,2),(2,5);
SELECT * FROM products;
```
Sketch (no need to run) a Postgres function full_name(first, last) that returns concatenated text. Why can't you do this in pure SQLite SQL?

Postgres function full_name(first,last) এর sketch দিন। SQLite-এ কেন এটি pure SQL-এ লেখা যায় না?
✨ Show Answer
```
-- Postgres only:
CREATE FUNCTION full_name(f TEXT, l TEXT)
RETURNS TEXT LANGUAGE sql IMMUTABLE AS
$$ SELECT f || ' ' || l $$;
```
SQLite has no CREATE FUNCTION statement; UDFs must be registered from the host language (C, Python, JavaScript). In pure SQL you would just inline the expression: SELECT first || ' ' || last AS full_name FROM users;.

SQLite-এ CREATE FUNCTION বলে কিছু নেই — function host language (C, Python, JS) থেকে register করতে হয়। SQL-এর মধ্যে শুধু concatenation expression সরাসরি লিখতে হয়।
A junior dev wants to add a trigger that sends an SMS when an order is placed. Why is this a bad idea?

Order insert হলে SMS পাঠাতে trigger লেখা কেন খারাপ idea?

✨ Show Answer

Answer: Triggers run inside the transaction. If the SMS gateway is slow or down, every INSERT INTO orders blocks or fails. If the transaction rolls back later, the SMS has already been sent — you cannot un-send it. The right design is: write the order, write a row to an outbox table, and let a separate worker process pick that up and send the SMS asynchronously.

Trigger transaction-এর ভেতর চলে। SMS gateway slow হলে প্রতিটি order আটকে যাবে, এবং transaction rollback হলেও SMS তো আগেই চলে গেছে — সেটি আর ফেরানো যায় না। সঠিক design হলো একটি outbox table-এ row লিখে রাখা, আর আলাদা worker সেটি পড়ে পরে SMS পাঠাবে।
Write an INSTEAD OF trigger on a SQLite view v_users that translates inserts into the underlying users table.

View-এর উপর INSTEAD OF INSERT trigger লিখুন যা actual users table-এ insert করবে।
✨ Show Answer
ans12.sql
```
INSERT INTO v_users(name,email) VALUES('Arif','arif@example.com');
SELECT * FROM users;
```
Without INSTEAD OF, views in SQLite are read-only. INSTEAD OF lets the view behave like a virtual table for writes.

Summary — Module 21

Stored procedures, functions, and triggers let you push logic into the database itself. Procedures (called explicitly by the app) and functions (callable from inside SELECT) are not natively supported in pure-SQL SQLite — but PostgreSQL gives you both via PL/pgSQL. Triggers, on the other hand, work everywhere — and SQLite's CREATE TRIGGER handles the most useful patterns (validation, audit logs, derived counters). Power comes with cost: a trigger-heavy database is hard to reason about, so use them surgically and keep them documented.

Procedure এবং function database-এ logic রাখার শক্তিশালী টুল — কিন্তু SQLite এদের pure SQL-এ support করে না, শুধু PostgreSQL-এ লেখা যায়। Trigger সব major database-এ আছে, এবং SQLite-এ CREATE TRIGGER দিয়ে validation, audit log, derived counter — সব সাধারণ pattern লেখা যায়। তবে trigger বেশি ব্যবহার করলে database "ভূতের বাড়ি" হয়ে যায় — ছোট ছোট রাখুন, নথিভুক্ত করুন।

Next Module → Constraints, Defaults & Domains — schema নিজেই কীভাবে data সঠিক রাখে।

Previous (Module 20) Next: Constraints, Defaults & Domains