01
কেন Trigger দরকার?
১৫ মিনিট
Trigger = database-এর automatic action যেটা নির্দিষ্ট event ঘটলে নিজে থেকে execute হয়।
EVENT → TRIGGER → AUTOMATIC ACTION
Employee Salary Updated → Trigger → Write Audit Record
Bank
Update Balance → Record Txn → Audit
১০০ app — কেউ audit ভুলে গেলে?
E-commerce
Order Created → Reduce Stock → Activity
HR / Hospital
Salary/Patient change → History
Security
Balance UPDATE → Who/What/When
Trigger ≈ database-এর automatic security guard।
02
Manual SQL vs Trigger
১০ মিনিট
Without: UPDATE → Developer remembers audit → INSERT audit (forget risk)
With: UPDATE → Trigger auto → Audit created
| Manual | Trigger |
|---|---|
| Developer calls | DB fires automatically |
| Easy to forget | Automatic |
| Duplicated logic | Centralized |
| App-dependent | Database-level |
| Explicit execution | Event-driven |
03
Procedure vs Trigger
১০ মিনিট
| Feature | Stored Procedure | Trigger |
|---|---|---|
| Manual execute / CALL | Yes | No |
| Automatic | No | Yes |
| Fired by event | No | Yes |
| Parameters | Yes | No traditional params |
| Purpose | Reusable workflow | Event-driven action |
PROCEDURE: User → CALL → Procedure → Result
TRIGGER: User → INSERT/UPDATE/DELETE → Trigger auto → Action
PROCEDURE → "Do this when I call you."
TRIGGER → "Do this when this event happens."
04
Events: INSERT / UPDATE / DELETE
১০ মিনিট
DATABASE EVENT
├── INSERT → new record (audit hire)
├── UPDATE → change (salary history)
└── DELETE → remove (keep deleted info)
05
BEFORE vs AFTER
১৫ মিনিট
BEFORE: Event → BEFORE Trigger → Actual INSERT/UPDATE/DELETE
AFTER: Event → Actual event → AFTER Trigger
| BEFORE | AFTER |
|---|---|
| Event-এর আগে | Event-এর পরে |
| Validation / transform | Audit / logging |
| Data preparation | History recording |
BEFORE = আগে · AFTER = পরে
06
Trigger Matrix
৮ মিনিট
| Timing | INSERT | UPDATE | DELETE |
|---|---|---|---|
| BEFORE | Yes | Yes | Yes |
| AFTER | Yes | Yes | Yes |
TRIGGER → BEFORE|AFTER → INSERT|UPDATE|DELETE (৬ common combo)
07
First Trigger
১৫ মিনিট
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //
DELIMITER ;
INSERT → BEFORE INSERT TRIGGER → NEW.created_at = NOW() → INSERT continues
FOR EACH ROW = প্রতি affected row-এর জন্য। DELIMITER client command।08
NEW (ও OLD preview)
১০ মিনিট
| Event | OLD | NEW |
|---|---|---|
| INSERT | ❌ | ✅ |
| UPDATE | ✅ | ✅ |
| DELETE | ✅ | ❌ |
INSERT → NEW · UPDATE → OLD + NEW · DELETE → OLD
এই table মুখস্থ — Trigger-এর সবচেয়ে গুরুত্বপূর্ণ ধারণা।
09
AFTER INSERT Audit
১০ মিনিট
CREATE TABLE employee_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
action_type VARCHAR(20),
action_time DATETIME
);
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit(employee_id, action_type, action_time)
VALUES(NEW.employee_id, 'INSERT', NOW());
END //
DELIMITER ;
employee_id=101 · action_type=INSERT · action_time=now
Employee Insert → AFTER INSERT Trigger → Audit Table
10
BEFORE UPDATE Validation
১৫ মিনিট
Salary negative হতে পারবে না।
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END //
DELIMITER ;
UPDATE → BEFORE UPDATE → Check NEW.salary → Fix if invalid → UPDATE
11
OLD vs NEW (UPDATE)
১৫ মিনিট
OLD.salary = 50000
│ UPDATE
↓
NEW.salary = 60000
Audit/history-এর জন্য OLD + NEW দুটোই লাগে।
12
Salary History Trigger
১৫ মিনিট
CREATE TABLE salary_history (
history_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at DATETIME
);
DELIMITER //
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_history(employee_id, old_salary, new_salary, changed_at)
VALUES(NEW.employee_id, OLD.salary, NEW.salary, NOW());
END IF;
END //
DELIMITER ;
101 · old 60000 · new 65000
13
DELETE Trigger (OLD)
১০ মিনিট
DELIMITER //
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit(employee_id, action_type, action_time)
VALUES(OLD.employee_id, 'DELETE', NOW());
END //
DELIMITER ;
DELETE-এ NEW নেই — row চলে যাবে বলে OLD ব্যবহার করো।
14
Audit System
১৫ মিনিট
INSERT → Audit INSERT LOG
UPDATE → Audit UPDATE LOG
DELETE → Audit DELETE LOG
Who · What · When · Old · New
(Finance · Banking · HR · Healthcare · Gov)
Actual DB user/session capture setup-dependent — carefully design।
15
BEFORE INSERT Normalize
১০ মিনিট
SET NEW.employee_name = TRIM(NEW.employee_name);
Raw Input → BEFORE Trigger → Clean Value → Insert
প্রতিটি cleaning-এর জন্য Trigger নয় — app validation / constraints-ও ভাবি।
16
Stock Reduce Trigger
১৫ মিনিট
Order Item Added → AFTER INSERT → Reduce Product Stock
DELIMITER //
CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET stock = stock - NEW.quantity
WHERE product_id = NEW.product_id;
END //
DELIMITER ;
Stock negative? Concurrency? Transaction? — carefully test।
17
Stock Validation + SIGNAL
১০ মিনিট
Requested Qty → Check Stock → Enough? Yes Allow / No Reject
IF NEW.quantity > (
SELECT stock FROM products
WHERE product_id = NEW.product_id
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient stock';
END IF;
18
SIGNAL Errors
১০ মিনিট
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid salary';
Invalid Condition → SIGNAL → Custom Error → Operation Stops
Use: invalid qty · negative salary · stock shortage · bad business state
19
Multiple Triggers
১০ মিনিট
BEFORE INSERT · AFTER INSERT · BEFORE UPDATE · AFTER UPDATE …
অনেক Trigger → debug কঠিন। কম কিন্তু meaningful রাখো।
20
SHOW Triggers
৫ মিনিট
SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
21
DROP TRIGGER
৫ মিনিট
DROP TRIGGER trigger_name;
DROP TRIGGER IF EXISTS trigger_name;
Production-এ drop-এর আগে dependency check।
22
Table / View / Proc / Trigger
৮ মিনিট
| Object | Main Job |
|---|---|
| Table | Store |
| View | Show |
| Stored Procedure | Do when called |
| Trigger | Do automatically on event |
TABLE STORE · VIEW SHOW · PROCEDURE CALL→DO · TRIGGER EVENT→DO
23
Trigger vs App Code
৮ মিনিট
App: INSERT → App remembers audit → Audit INSERT (forget risk)
DB: Any App → INSERT → Trigger → Audit (same rule for all)
Hidden DB logic → app developers-এর debugging কঠিন হতে পারে।
24
When to Use
৬ মিনিট
- Automatic audit logging
- DB-level must-happen actions
- Validation at database level
- Multiple apps share same rule
- History tracking
- Related automatic updates
25
When to Avoid
৬ মিনিট
- Extremely complex business logic
- Belongs in app/service layer
- Hidden side effects
- Too many triggers / hard debug
- Expensive row-level on bulk ops
- Clearer explicit workflow exists
Trigger powerful — কিন্তু সব সমস্যার solution নয়।
26
Performance
৮ মিনিট
1 INSERT → Trigger → 1 more INSERT
1,000,000 INSERT → 1,000,000 trigger executions
Keep logic small · avoid nested queries · test bulk · index · monitor
27
Side Effects
৬ মিনিট
Developer sees: UPDATE employees
DB also does: INSERT salary_history (hidden side effect)
Benefit এবং risk দুটোই — document করো।
28
Debugging Workflow
৮ মিনিট
Problem → SHOW TRIGGERS → SHOW CREATE TRIGGER
→ Check Event → Timing → OLD/NEW → SQL → Test small data
29
Common Mistakes (১৫)
১২ মিনিট
- Trigger CALL করার চেষ্টা
- BEFORE/AFTER confuse
- INSERT-এ OLD
- DELETE-এ NEW
- FOR EACH ROW ভুলে যাওয়া
- Wrong DELIMITER
- BEGIN/END ভুলে যাওয়া
- Duplicate trigger names
- Unexpected recursive effects
- Too much logic
- Ignore performance
- Audit table deps forget
- Bulk ops না test
- Trigger = সব validation ধরা
- Behavior document না করা
30
Master Flowchart
৫ মিনিট
DATABASE EVENT → INSERT|UPDATE|DELETE
→ BEFORE ACTION → ACTUAL EVENT → AFTER ACTION
31
Mini Project — Employee Audit
১৫ মিনিট
Tables: employees · employee_audit · salary_history
- AFTER INSERT → audit log
- AFTER UPDATE → record update
- AFTER salary change → OLD/NEW history
- BEFORE DELETE → store deleted info
- BEFORE salary UPDATE → prevent invalid
Test INSERT / UPDATE / DELETE → verify audit + history
32
E-com Challenge
১০ মিনিট
products · orders · order_items · stock_history
Order Item INSERT → Reduce Stock → Stock History
Insufficient → SIGNAL reject
Ask: Event? BEFORE/AFTER? NEW/OLD? Auto what?
33
Live Lab (১৯ steps)
১৫ মিনিট
- Create employee table
- Create audit table
- First Trigger
- Insert employee
- Check audit
- UPDATE Trigger
- Update salary
- Check OLD/NEW
- DELETE Trigger
- Delete employee
- Check audit
- Validation Trigger
- SIGNAL
- Test invalid
- SHOW TRIGGERS
- SHOW CREATE
- DROP
- Recreate
- Complete mini project
34
Activities (২০)
১০ মিনিট
Act 1 — Procedure or Trigger?
CALL → Proc · Event → Trigger
Act 2 — Which event?
INSERT / UPDATE / DELETE
Act 3 — BEFORE or AFTER?
Validate before · audit after
Act 4 — OLD or NEW?
INSERT NEW · DELETE OLD · UPDATE both
Act 5 — Predict audit output
NEW.employee_id + action + time
Act 6 — Fix broken Trigger
FOR EACH ROW / DELIMITER / OLD-NEW
Act 7 — Design salary history
AFTER UPDATE · IF OLD<>NEW
Act 8 — Design stock Trigger
AFTER INSERT order_items
Act 9 — Security problem
Missing audit on balance change
Act 10 — Perf problem
Heavy logic × million rows
Act 11 — Will Trigger fire?
Only matching event on that table
Act 12 — Choose DML event
Business action → matching event
Act 13 — Explain workflow
BEFORE → event → AFTER
Act 14 — Duplicate name
DROP or rename
Act 15 — Build audit system
3 triggers INSERT/UPDATE/DELETE
Act 16 — Stock validation
BEFORE + SIGNAL
Act 17 — Trigger vs Procedure
Auto vs CALL
Act 18 — Trigger vs App
DB-level vs forgettable app code
Act 19 — Review prod Trigger
Side effects · perf · docs
Act 20 — Mini project
5 employee triggers
35
Predict (১৫)
১০ মিনিট
Predict: salary 60000→70000 audit?
OLD 60000 NEW 70000 in history
Predict: DELETE — OLD or NEW id?
OLD.employee_id
Predict: INSERT Trigger uses OLD.salary?
Error / invalid — no OLD
Predict: CALL after_employee_insert?
Cannot CALL trigger
Predict: AFTER INSERT audit row?
INSERT action + NEW id
Predict: NEW.salary = -100 with clamp?
Becomes 0 if BEFORE sets
Predict: SHOW TRIGGERS shows?
List of triggers
Predict: DROP TRIGGER effect?
Auto action stops
Predict: Stock insufficient + SIGNAL?
Insert rejected
Predict: Same salary UPDATE history?
IF skips — no history row
Predict: BEFORE vs AFTER for created_at?
BEFORE INSERT
Predict: Million inserts cost?
Million trigger runs
Predict: Procedure vs Trigger fire?
Proc needs CALL
Predict: TRIM name when?
BEFORE INSERT
Predict: Side effect of salary UPDATE?
Hidden INSERT history
36
Debug (১০)
১০ মিনিট
Bug: CREATE TRIGGER … BEFORE INSERT ON t BEGIN … (no FOR EACH ROW)
Add FOR EACH ROW
Bug: AFTER DELETE … SET NEW.status
DELETE has no NEW — use OLD
Bug: BEFORE INSERT … use OLD.id
INSERT has no OLD
Bug: CALL my_trigger()
Triggers are not CALL-ed
Bug: Forget DELIMITER
Body ends at first ;
Bug: Forget BEGIN/END
Incomplete block
Bug: Duplicate trigger name
DROP IF EXISTS then CREATE
Bug: AFTER INSERT set NEW.created_at
Too late — use BEFORE
Bug: Heavy SELECT in trigger × bulk
Perf risk — simplify
Bug: No docs on side effects
Document hidden writes
37
Interview (৩০)
১২ মিনিট
IV 1. Trigger কী?
Event-driven automatic DB action · no CALL
IV 2. কেন?
Audit · enforce · multi-app rules
IV 3. vs Procedure?
Auto vs CALL
IV 4. vs View?
DO on event vs SHOW
IV 5. Events?
INSERT UPDATE DELETE
IV 6. BEFORE?
Before actual event
IV 7. AFTER?
After actual event
IV 8. OLD?
Previous/deleted values
IV 9. NEW?
New/incoming values
IV 10. OLD vs NEW?
See matrix
IV 11. INSERT + OLD?
No
IV 12. DELETE + NEW?
No
IV 13. FOR EACH ROW?
Per affected row
IV 14. Result set from trigger?
Not for client like SELECT procs
IV 15. Call Procedure from Trigger?
Possible — careful design
IV 16. Modify other table?
Yes (audit/history)
IV 17. IF in Trigger?
Yes
IV 18. SIGNAL?
Custom error · stop op
IV 19. Inspect?
SHOW TRIGGERS / SHOW CREATE
IV 20. Delete?
DROP TRIGGER
IV 21. Multiple?
Yes — keep few
IV 22. Disadvantages?
Hidden · hard debug · perf
IV 23. Perf impact?
Per-row cost on bulk
IV 24. Audit Triggers?
Log INSERT/UPDATE/DELETE
IV 25. History Triggers?
OLD→NEW salary etc.
IV 26. Avoid when?
Complex / app-owned / bulk-heavy
IV 27. vs App logic?
Central vs scattered
IV 28. Security help?
Enforce audit always
IV 29. Side effect?
Hidden extra writes
IV 30. Real use?
Salary history / stock check
38
MCQ (২৫)
১০ মিনিট
MCQ 1. Trigger is? A) CALL program B) auto on event C) VIEW D) INDEX
B
MCQ 2. CALL Trigger? A) yes B) no C) only OUT D) only IN
B
MCQ 3. Events? A) INSERT/UPDATE/DELETE B) only SELECT C) only DROP D) only GRANT
A
MCQ 4. BEFORE means? A) after event B) before event C) never D) only DROP
B
MCQ 5. AFTER means? A) before B) after event C) only CALL D) only VIEW
B
MCQ 6. INSERT has? A) OLD only B) NEW only C) both D) neither
B
MCQ 7. DELETE has? A) NEW B) OLD C) both D) neither
B
MCQ 8. UPDATE has? A) OLD+NEW B) only NEW C) only OLD D) none
A
MCQ 9. FOR EACH ROW? A) per row B) once ever C) DROP D) VIEW
A
MCQ 10. SIGNAL does? A) custom error B) create view C) CALL D) GRANT
A
MCQ 11. Audit typically? A) AFTER B) never C) only SELECT D) only LIMIT
A
MCQ 12. Clamp salary? A) BEFORE UPDATE B) only DROP C) only LIKE D) only REGEXP
A
MCQ 13. Inspect? A) SHOW TRIGGERS B) CALL TRIGGER C) DROP DB D) TRUNCATE
A
MCQ 14. Delete trigger? A) DROP TRIGGER B) DROP TABLE always C) DELETE VIEW D) ALTER INDEX
A
MCQ 15. Proc vs Trigger? A) CALL vs event B) same C) both need CALL D) both are views
A
MCQ 16. Perf risk?
Per-row on bulk
MCQ 17. Side effect?
Hidden extra DML
MCQ 18. Stock reject?
SIGNAL in BEFORE
MCQ 19. History needs?
OLD and NEW
MCQ 20. created_at set?
BEFORE INSERT NEW
MCQ 21. Focus of class? A) TRIGGER B) Python C) NLP D) REGEXP reteach
A
MCQ 22. Avoid when?
Over-complex / hard debug
MCQ 23. Multi-app rule?
DB trigger helps
MCQ 24. Memory INSERT?
NEW
MCQ 25. Mental model?
EVENT → AUTO DO
39
Viva (২০)
৮ মিনিট
Viva 1. Trigger কী?
Auto action on DB event
Viva 2. CALL করা যায়?
না
Viva 3. কখন fire?
INSERT/UPDATE/DELETE
Viva 4. BEFORE?
Event-এর আগে
Viva 5. AFTER?
Event-এর পরে
Viva 6. OLD?
Previous/deleted
Viva 7. NEW?
New values
Viva 8. UPDATE-এ দুটো কেন?
Change track
Viva 9. DELETE-এ কোনটি?
OLD
Viva 10. INSERT-এ কোনটি?
NEW
Viva 11. Audit কেন?
Always log
Viva 12. Perf problem?
হ্যাঁ — bulk
Viva 13. Proc vs Trigger?
CALL vs event
Viva 14. SIGNAL?
Custom error
Viva 15. SHOW?
SHOW TRIGGERS
Viva 16. DROP?
DROP TRIGGER
Viva 17. FOR EACH ROW?
Per row
Viva 18. Side effect?
Hidden writes
Viva 19. Avoid?
Too complex
Viva 20. One line?
Event → automatic action · no CALL
40
Homework — Bank Audit
৮ মিনিট
Tables: accounts · transactions · account_audit
- AFTER INSERT account → audit
- AFTER UPDATE → track balance changes
- BEFORE balance UPDATE → prevent negative (SIGNAL)
- BEFORE DELETE → save deleted account info
Must use: BEFORE · AFTER · INSERT · UPDATE · DELETE · OLD · NEW · SIGNAL · audit table
41
Final Challenge
১০ মিনিট
Requirement
Stock কখনো negative নয় + প্রতিটি stock change-এর history — আগে design, পরে solution।
Order Item Insert → BEFORE INSERT → Check Stock
→ Enough? YES Allow / NO SIGNAL
→ Reduce Stock → AFTER → Stock History
42
Revision + Memory Map
৫ মিনিট
| Concept | Meaning |
|---|---|
| Trigger | Automatic DB action |
| Event | INSERT / UPDATE / DELETE |
| BEFORE / AFTER | আগে / পরে |
| NEW / OLD | New / previous-deleted |
| FOR EACH ROW | Per affected row |
| SIGNAL | Custom error |
| SHOW / DROP | Inspect / delete |
TABLE STORE · VIEW SHOW · PROCEDURE CALL→DO · TRIGGER EVENT→DO
INSERT→NEW · UPDATE→OLD+NEW · DELETE→OLD
Trigger manually CALL করতে হয় না — event ঘটলেই fire করে।Trigger = event-driven automatic action — no CALL.