SQL Triggers

Learn BEFORE, AFTER triggers with real-world examples.

SQL Intermediate 60 min

🧠 Introduction: SQL Triggers

A SQL Trigger is a special database object that automatically executes when a specific event occurs on a table.

Triggers are commonly used to enforce business rules, validate data, maintain audit logs, and ensure consistency without manual execution.

📝 Trigger Executes Automatically On:

🎯 Topic 1: Trigger Events & Timing

Triggers fire based on event and timing.

✅ Trigger Types:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

💻 Example: BEFORE INSERT Trigger


                            CREATE TRIGGER before_employee_insert

                            BEFORE INSERT ON Employees

                            FOR EACH ROW

                            BEGIN

                              IF NEW.Salary < 0 THEN

                                SIGNAL SQLSTATE '45000';

                              END IF;

                            END;

🔐 Topic 2: NEW & OLD Keywords

MySQL provides NEW and OLD keywords to access row values inside triggers.

• NEW.column → New row value (INSERT / UPDATE)
• OLD.column → Old row value (UPDATE / DELETE)

💻 Example: AFTER UPDATE Trigger


                            IF OLD.Salary <> NEW.Salary THEN

                              INSERT INTO Salary_Log VALUES (...);

                            END IF;

↩️ Topic 3: BEFORE Triggers

BEFORE triggers run before data modification. They are mainly used for validation and restriction.

• Validate data
• Prevent invalid operations
• Modify incoming values

💻 Example: BEFORE DELETE


                            IF OLD.Department = 'HR' THEN

                              SIGNAL SQLSTATE '45000';

                            END IF;

🧷 Topic 4: AFTER Triggers

AFTER triggers execute after the data change. Mostly used for logging and audit tracking.

• Maintain audit tables
• Track changes
• Business analytics

💻 Example: AFTER INSERT


                            INSERT INTO Employee_Audit

                            VALUES (NEW.EmployeeID, 'INSERT', NOW());

🚦 Topic 5: Trigger Rules & Limitations

Triggers have strict rules in MySQL / MariaDB.

• COMMIT / ROLLBACK ❌ not allowed
• FOR EACH ROW is mandatory
• Cannot call another trigger
• No parameters allowed

📚 Module Summary

Triggers execute automatically
Used for validation and logging
BEFORE → validation
AFTER → auditing
NEW & OLD access row values

🤔 Interview Q&A

A trigger is an automatic SQL program that executes when an event occurs on a table.

BEFORE triggers run before data change, AFTER triggers run after data change.

NEW refers to new row values, OLD refers to existing row values.

No. Transaction control statements are not allowed inside triggers.