Lesson 15 � Automation

DATA KI
AUTO GUARD.

Trigger automatic action hai � data insert hone pe, update hone pe kuch kaam ho jaata hai bina tumhare bole. Audit logging, validation, cascading changes � sab triggers se hota hai. Stored procedure ka advanced version jo database ke andar hi rehta hai.

WHY: Trigger kyun zaroori hai?

Socho tum ek school management system bana rahe ho. Jab bhi naya student aata hai, tumhe uska record ek audit log mein bhi daalna hai. Agar tum har INSERT ke baad manually ek aur INSERT karo ge � galti hogi, bhool jaoge. Trigger se yeh kaam automatic ho jaata hai � student aaya, trigger ne log kar diya. Bina kisi extra query ke.

HOW: Trigger banana aur use karna

Trigger banane ka syntax stored procedure jaisa hai � CREATE TRIGGER se shuru hota hai. Lekin trigger tum call nahi karte � database khud call karta hai jab specified event hoti hai.

1. AFTER INSERT trigger � Audit logging

Naya student insert hone pe audit log mein entry daalo:

-- After INSERT trigger
CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
 INSERT INTO audit_log (action, student_id, created_at)
 VALUES ('NEW STUDENT', NEW.id, NOW());
END;

2. BEFORE INSERT trigger � Validation

Data insert se pehle validate karo � age 16 se kam ho toh reject karo:

-- Before INSERT trigger (validation)
CREATE TRIGGER validate_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
 IF NEW.age < 16 THEN
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Age must be 16 or above';
 END IF;
END;

3. AFTER UPDATE trigger � History tracking

Marks update hone pe purana aur naya marks history mein save karo:

-- After UPDATE trigger
CREATE TRIGGER after_marks_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
 IF OLD.marks != NEW.marks THEN
 INSERT INTO marks_history (student_id, old_marks, new_marks, changed_at)
 VALUES (NEW.id, OLD.marks, NEW.marks, NOW());
 END IF;
END;

4. BEFORE DELETE trigger � Cleanup

Student delete hone se pehle uske related records bhi hatao:

-- Before DELETE trigger
CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
 DELETE FROM enrollments WHERE student_id = OLD.id;
 DELETE FROM marks WHERE student_id = OLD.id;
END;

5. Trigger drop karo

Agar trigger ki zaroorat nahi rahi:

-- Trigger hatao
DROP TRIGGER after_student_insert;

-- MySQL mein check karo kaunse triggers hain
SHOW TRIGGERS;

-- PostgreSQL mein
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
WHERE event_object_schema = 'public';

6. Trigger disable / enable karo (MySQL)

Temporary trigger disable karna ho toh:

-- Trigger disable karo
ALTER TABLE students DISABLE TRIGGER after_student_insert;

-- Trigger enable karo
ALTER TABLE students ENABLE TRIGGER after_student_insert;

-- PostgreSQL mein ek trigger disable
ALTER TABLE students DISABLE TRIGGER validate_age;

Trigger execution flow

Trigger kaise fire hota hai � step by step:

-- INSERT query chali
INSERT INTO students (name, age, marks) VALUES ('Rahul', 20, 92.5);

-- Kya hota hai internally:

-- Step 1: BEFORE INSERT trigger fire
-- validate_age trigger check karega
-- Agar age < 16 ✓ error throw, INSERT fail
-- Agar age >= 16 ✓ INSERT proceed

-- Step 2: Row actually inserted in students table

-- Step 3: AFTER INSERT trigger fire
-- after_student_insert trigger chalega
-- Audit log mein entry aa jaayegi

-- Flow: INSERT ✓ BEFORE ✓ Table mein data ✓ AFTER

Try it: Audit logging trigger banao

Neeche ke editor mein triggers create karo. Audit logging trigger banao jo har INSERT pe log kare, aur validation trigger banao jo age check kare.

Common beginner mistakes

• Trigger body mein SELECT karna: Trigger mein SELECT mat karo jab tak koi variable mein store nahi kar rahe. Direct SELECT client ko result bhej deta hai jo trigger ke context mein unexpected hai.
• Recursive triggers: Agar trigger table mein INSERT kare aur usi table pe trigger laga ho � infinite loop ho jaayega. Hamesha ensure karo ki trigger dusre table pe action le, ya SET NEW.col = value se BEFORE trigger mein data modify karo without re-firing.
• BEFORE vs AFTER na samajhna: BEFORE mein data modify kar sakte ho (SET NEW.name = UPPER(NEW.name)), AFTER mein nahi. BEFORE = validation/default, AFTER = logging/audit.
• Trigger ka naam yaad na rakhna: Trigger drop karna ho toh naam pata hona chahiye. SHOW TRIGGERS se dekh lo pehle.
• Bahut zyada logic trigger mein daalna: Trigger simple hona chahiye � ek kaam karo. Complex business logic stored procedure mein rakho, trigger mein sirf automation ka kaam karo.