Q.9)

SQL Triggers Kya Hain?

Basic Concept:
SQL mein Trigger ek special tarah ka stored procedure hota hai jo tab automatically execute (fire) hota hai jab database table mein koi specific event hota hai. Yeh events INSERT, UPDATE, ya DELETE operations ho sakte hain.

Triggers ka use data integrity maintain karne, actions ko automate karne, aur bina manual intervention (insani dakhal) ke business rules enforce karne ke liye kiya jata hai. Yeh background mein apne aap chalte hain, isliye banking systems, student management systems aur e-commerce platforms (jahan real-time data change hota hai) mein yeh bahut useful hote hain.

Definition & Syntax

Trigger ek database object hai jo kisi table par specific DML (Data Manipulation Language) event aane par automatically chal jata hai.

Syntax:

CREATE TRIGGER trigger_name
[BEFORE | AFTER]
{INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
   -- SQL statements yahan aayengi
END;

Syntax ka Explanation:

• CREATE TRIGGER: Naya trigger banane ke liye use hota hai.
• trigger_name: Trigger ka pehchan (naam) dene ke liye.
• BEFORE | AFTER: Yeh batata hai ki trigger event se pehle chalega ya baad mein.
• INSERT | UPDATE | DELETE: Wo event jo is trigger ko activate karega.
• ON table_name: Jis table par yeh trigger lagana hai.
• FOR EACH ROW: Iska matlab hai ki trigger har us row ke liye chalega jisme badlaav (change) aayega.
• BEGIN...END: Iske beech wo SQL statements likhi jati hain jo automatically run hongi.

---

Suitable Example of Trigger (Step-by-Step)

Chaliye ek example dekhte hain jahan table mein koi record update hone par, uska 'Time' (updated_at) apne aap change ho jayega.

Step 1: Table Banana

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    updated_at TIMESTAMP
);

Step 2: Trigger Banana

Yeh trigger tab chalega jab bhi 'users' table mein koi record update hoga.

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END;

Example ka Explanation:

• Trigger ka naam update_timestamp hai.
• Yeh users table par UPDATE operation hone se pehle (BEFORE) execute hoga.
• Yeh automatically updated_at column mein current system ka time set kar dega.
• NEW keyword ka matlab hai update hone ke baad aane wali nayi value.

Step 3 & 4: Data Insert aur Update Karna

-- Pehle data insert karte hain
INSERT INTO users (id, name, email) 
VALUES (1, 'Amit', 'amit@example.com');

-- Ab email update karte hain (Trigger apne aap chalega!)
UPDATE users 
SET email = 'amit_new@example.com' 
WHERE id = 1;

Working of Trigger (Yeh kaise kaam karta hai):

1. Jaise hi UPDATE query chalti hai, trigger automatically activate ho jata hai.
2. Yeh system time uthata hai aur updated_at column mein daal deta hai.
3. Iske liye humein alag se time update karne ka code likhne ki zaroorat nahi padti, jisse human effort kam hota hai.

---

Types of Triggers (Triggers ke Prakaar)

1. DDL Triggers (Data Definition Language Triggers)

Yeh triggers tab fire hote hain jab database ke structure mein koi change hota hai (jaise CREATE, ALTER, ya DROP). Yeh schema mein hone wale changes ko monitor ya control karne ke liye hote hain.

CREATE TRIGGER prevent_table_changes
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    PRINT 'You are not allowed to modify table structure';
    ROLLBACK;
END;

• Explanation: Yeh trigger kisi bhi table ko banne, modify hone ya delete hone se rokat hai. Agar koi aisi koshish karta hai, toh ROLLBACK us action ko cancel kar deta hai. Yeh security ke liye bahut acha hai.

2. DML Triggers (Data Manipulation Language Triggers)

Yeh triggers tab fire hote hain jab table ke andar ke data ko modify kiya jata hai (jaise INSERT, UPDATE, ya DELETE). Inka use rules lagane aur logging ke liye hota hai.

CREATE TRIGGER prevent_update
ON students
FOR UPDATE, INSERT, DELETE
AS
BEGIN
    RAISERROR ('You cannot modify this table', 16, 1);
END;

• Explanation: Yeh students table mein kisi bhi tarah ke data change ko block kar dega. RAISERROR user ko ek error message dikhayega ki "Aap is table ko modify nahi kar sakte".

3. Logon Triggers

Yeh triggers tab chalte hain jab koi user database system mein login karta hai. Inka use login activities ko track karne aur security ke liye hota hai.

CREATE TRIGGER track_login
ON LOGON
AS
BEGIN
    PRINT 'User has logged in successfully';
END;

• Explanation: Jaise hi koi database mein enter karega, yeh trigger ek message dega. Yeh pata lagane ke liye acha hai ki kaun kab login kar raha hai.

---

Key Points (Mukhy Bindu)

1. Trigger ek automatic SQL procedure hai jo INSERT, UPDATE, ya DELETE par chalta hai.
2. Yeh database ke tasks automate karne aur data ko sahi (integrate) rakhne mein madad karta hai.
3. Triggers event ke BEFORE (pehle) ya AFTER (baad) chal sakte hain.
4. DDL triggers table/database ke structure changes (CREATE/DROP) ko handle karte hain.
5. DML triggers table ke andar ke data changes (INSERT/UPDATE) ko handle karte hain.
6. Logon triggers user ki login activity ko monitor karte hain.