Chapter 16: Triggers & Events
Triggers and Events are like hidden robots inside MySQL that watch for certain things to happen and then automatically run code for you.
- Triggers → React to data changes (INSERT, UPDATE, DELETE)
- Events → Run scheduled tasks at specific times (like cron jobs in the database)
These two features make your database self-maintaining, audit-proof, and super efficient.
Let’s dive in step-by-step!
1. What is a Trigger? (Real-life analogy)
Imagine your coaching class has a rule: “Every time a new student enrolls (INSERT into enrollments), automatically update the student’s last_enrollment_date in the students table.”
Instead of remembering to do this manually every time, you create a trigger that automatically runs whenever someone inserts into enrollments.
Triggers fire automatically when:
- INSERT → new row added
- UPDATE → row changed
- DELETE → row removed
And they can run BEFORE the action (to modify data) or AFTER (to log or react).
2. CREATE TRIGGER – Basic Syntax
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DELIMITER // CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW BEGIN -- Your SQL code here -- Use NEW.column_name (for INSERT/UPDATE) -- Use OLD.column_name (for UPDATE/DELETE) END // DELIMITER ; |
Important terms:
- NEW → refers to the new row being inserted/updated
- OLD → refers to the old row being updated/deleted
Example 1 – BEFORE INSERT trigger: Automatically set enrollment_date to today
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE TRIGGER before_enroll_set_date BEFORE INSERT ON enrollments FOR EACH ROW BEGIN SET NEW.enrollment_date = CURDATE(); END // DELIMITER ; |
Now whenever you do:
|
0 1 2 3 4 5 6 7 |
INSERT INTO enrollments (student_id, course_id, fees_paid) VALUES (1, 2, 3000.00); |
The enrollment_date will automatically be set to today — even if you forget to mention it!
Example 2 – AFTER INSERT trigger: Update student’s last_enrollment_date First, add a column to students:
|
0 1 2 3 4 5 6 |
ALTER TABLE students ADD COLUMN last_enrollment_date DATE NULL; |
Now create the trigger:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DELIMITER // CREATE TRIGGER after_enroll_update_student AFTER INSERT ON enrollments FOR EACH ROW BEGIN UPDATE students SET last_enrollment_date = CURDATE() WHERE student_id = NEW.student_id; END // DELIMITER ; |
Now every enrollment automatically updates the student’s last enrollment date!
Example 3 – BEFORE UPDATE trigger: Prevent fees_paid from being negative
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DELIMITER // CREATE TRIGGER before_update_fees_check BEFORE UPDATE ON enrollments FOR EACH ROW BEGIN IF NEW.fees_paid < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Fees paid cannot be negative!'; END IF; END // DELIMITER ; |
Try:
|
0 1 2 3 4 5 6 |
UPDATE enrollments SET fees_paid = -100 WHERE enrollment_id = 1; |
→ You’ll get an error! (Great for data validation)
Example 4 – AFTER DELETE trigger: Log deleted enrollments First, create a log table:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE enrollment_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, course_id INT, action VARCHAR(50), action_date DATETIME DEFAULT NOW() ); |
Now the trigger:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DELIMITER // CREATE TRIGGER after_delete_enroll_log AFTER DELETE ON enrollments FOR EACH ROW BEGIN INSERT INTO enrollment_log (student_id, course_id, action) VALUES (OLD.student_id, OLD.course_id, 'DELETED'); END // DELIMITER ; |
Delete a row:
|
0 1 2 3 4 5 6 |
DELETE FROM enrollments WHERE enrollment_id = 1; |
Check log:
|
0 1 2 3 4 5 6 |
SELECT * FROM enrollment_log; |
3. CREATE EVENT – Scheduled Tasks (Like Cron Jobs)
Events are scheduled SQL tasks that run at specific times or intervals.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DELIMITER // CREATE EVENT event_name ON SCHEDULE EVERY interval [STARTS timestamp] DO BEGIN -- Your SQL code END // DELIMITER ; |
Example 1 – Daily event: Mark inactive students who haven’t enrolled in 365 days
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DELIMITER // CREATE EVENT deactivate_old_students ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO BEGIN UPDATE students s LEFT JOIN enrollments e ON s.student_id = e.student_id SET s.is_active = FALSE WHERE e.student_id IS NULL AND s.join_date < DATE_SUB(CURDATE(), INTERVAL 365 DAY); END // DELIMITER ; |
This runs every day and automatically deactivates students who haven’t enrolled in over a year.
Example 2 – Monthly event: Reset fees_paid to 0 on the 1st of every month (for demo)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DELIMITER // CREATE EVENT monthly_fee_reset ON SCHEDULE EVERY 1 MONTH STARTS '2026-02-01 00:00:00' DO BEGIN UPDATE enrollments SET fees_paid = 0.00; END // DELIMITER ; |
4. Scheduling & Monitoring Events
Enable event scheduler (very important – off by default!):
|
0 1 2 3 4 5 6 |
SET GLOBAL event_scheduler = ON; |
Check if it’s running:
|
0 1 2 3 4 5 6 |
SHOW VARIABLES LIKE 'event_scheduler'; |
See all events:
|
0 1 2 3 4 5 6 |
SHOW EVENTS; |
Drop an event:
|
0 1 2 3 4 5 6 |
DROP EVENT IF EXISTS deactivate_old_students; |
Disable an event temporarily:
|
0 1 2 3 4 5 6 |
ALTER EVENT deactivate_old_students DISABLE; |
That’s it for Chapter 16! 🎉 You now know how to make your database automatically enforce rules, log changes, clean up old data, and run scheduled jobs — like a true professional DBA!
Homework for today (do it right now – very important!) Run these and paste the output of the final SELECTs:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- 1. Create BEFORE INSERT trigger to auto-set enrollment_date DELIMITER // CREATE TRIGGER auto_set_enroll_date BEFORE INSERT ON enrollments FOR EACH ROW BEGIN SET NEW.enrollment_date = CURDATE(); END // DELIMITER ; -- 2. Insert without specifying date → see if trigger works INSERT INTO enrollments (student_id, course_id, fees_paid) VALUES (2, 3, 4500.00); SELECT * FROM enrollments WHERE student_id = 2; -- 3. Create AFTER INSERT trigger to update last_enrollment_date ALTER TABLE students ADD COLUMN last_enrollment_date DATE NULL; DELIMITER // CREATE TRIGGER update_last_enroll AFTER INSERT ON enrollments FOR EACH ROW BEGIN UPDATE students SET last_enrollment_date = CURDATE() WHERE student_id = NEW.student_id; END // DELIMITER ; -- 4. Insert another enrollment and check INSERT INTO enrollments (student_id, course_id, fees_paid) VALUES (2, 1, 6000.00); SELECT full_name, last_enrollment_date FROM students WHERE student_id = 2; |
Paste the results of the two SELECTs — I’ll check if the triggers worked!
