MySQL: MySQL Triggers and the Event Scheduler

Last updated: 2026-08-26

A trigger is a stored procedure that runs automatically—it is triggered automatically when data changes.

This lesson covers triggers and the event scheduler.

graph TB
    A[Data Change Events] --> B{Trigger Conditions}
    B -->|BEFORE| C[Triggered before the operation]
    B -->|AFTER| D[Triggered after the operation]
    C --> E[BEFORE INSERT<br/>Pre-insertion Validation/Edit]
    C --> F[BEFORE UPDATE<br/>Pre-update Validation/Edit]
    C --> G[BEFORE DELETE<br/>Verify Before Deleting]
    D --> H[AFTER INSERT<br/>Post-insertion synchronization]
    D --> I[AFTER UPDATE<br/>Update Log]
    D --> J[AFTER DELETE<br/>Clean Up After Deletion]
    A --> K[Event Scheduler]
    K --> K1[Scheduled Execution]
    K --> K2[A one-time event]

graph TB
    A[Data Change Events] --> B{Trigger Conditions}
    B -->|BEFORE| C[Triggered before the operation]
    B -->|AFTER| D[Triggered after the operation]
    C --> E[BEFORE INSERT<br/>Pre-insertion Validation/Edit]
    C --> F[BEFORE UPDATE<br/>Pre-update Validation/Edit]
    C --> G[BEFORE DELETE<br/>Verify Before Deleting]
    D --> H[AFTER INSERT<br/>Post-insertion synchronization]
    D --> I[AFTER UPDATE<br/>Update Log]
    D --> J[AFTER DELETE<br/>Clean Up After Deletion]
    A --> K[Event Scheduler]
    K --> K1[Scheduled Execution]
    K --> K2[A one-time event]

1. What You'll Learn

• BEFORE/AFTER triggers
• NEW/OLD keywords
• Trigger Management
• CREATE EVENT Event Scheduling

2. A True Story

(1) Pain Point: Manual operations often result in oversights

After the e-commerce system went live, the operations team complained daily: inventory wasn’t deducted after orders were placed, audit logs weren’t updated after price changes, and a backlog of expired sessions was slowing down queries. The development team kept adding logic to the business code, but someone always forgot—since the code for different services was written in isolation, there was simply no way to ensure consistency across the board.

(2) Solution Using Triggers and Events

Use triggers to automate actions at the database level: deduct inventory after an order is placed and log changes after modifications; use the event scheduler to periodically clean up expired data.

3. Trigger Basics

▶ Example: AFTER INSERT Trigger

DELIMITER //
CREATE TRIGGER trg_after_insert_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

Output:

Output displayed

▶ Example: BEFORE UPDATE Trigger

Output:

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

DELIMITER //
CREATE TRIGGER trg_before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    
    IF NEW.email != OLD.email THEN
        SET NEW.email_verified = FALSE;
    END IF;
END //
DELIMITER ;

Output:

Output displayed

4. The NEW and OLD Keywords

5. Trigger Management

-- View Triggers
SHOW TRIGGERS;

-- Delete Trigger
DROP TRIGGER IF EXISTS trg_after_insert_user;

6. Event Scheduler

▶ Example: Scheduled Tasks

Output:

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

-- Start the Event Scheduler
SET GLOBAL event_scheduler = ON;

-- Create an event that runs daily
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2026-07-04 02:00:00'
DO
    DELETE FROM temp_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Create a One-Time Event
CREATE EVENT evt_one_time
ON SCHEDULE AT '2026-07-10 00:00:00'
DO
    UPDATE promotions SET status = 'expired' WHERE end_date < CURDATE();

Output:

Output displayed

❓ FAQ

📖 Summary

• Triggers automatically execute when data changes
• BEFORE Triggers before the operation; AFTER Triggers after the operation
• NEW is the new value, OLD is the old value
• Event Scheduler implements scheduled tasks

📝 Exercises

1. Basic Question (Difficulty: ⭐): Create a trigger to automatically log changes to the user table.

2. Advanced Exercise (Difficulty: ⭐⭐): Create a BEFORE UPDATE trigger to automatically update the updated_at field.

3. Challenge (Difficulty: ⭐⭐⭐): Create an event scheduler that deletes log data from 30 days ago every day at midnight.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us