Scheduled Events in SQL – Automate Tasks with SQL Event Scheduler

What Are Scheduled Events in SQL?

Scheduled Events (also called SQL Events) are database tasks that run automatically at a specified time or interval, without manual intervention or external scripting.

Think of them as SQL's version of cron jobs. They're mainly used in MySQL (and MariaDB) to automate recurring operations like:

• Cleaning up old data
• Archiving records
• Generating summary reports
• Resetting counters
• Sending periodic notifications (when integrated)

When to Use Scheduled Events

Use scheduled events when you want to automate repetitive database tasks such as:

• Deleting outdated user sessions every night
• Backing up tables on a regular basis
• Updating daily statistics
• Automatically expiring or archiving data

Enabling the Event Scheduler in MySQL

The event scheduler must be turned on to use scheduled events.

Enable for the current session:

SET GLOBAL event_scheduler = ON;

To check if it's enabled:

SHOW VARIABLES LIKE 'event_scheduler';

Enable permanently in my.cnf:

event_scheduler=ON

Basic Syntax for Creating a Scheduled Event

CREATE EVENT event_name
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  -- SQL statements here;

Or to run periodically:

CREATE EVENT event_name
ON SCHEDULE EVERY 1 DAY
DO
  -- SQL statements here;

Example 1: Delete Old Login Sessions Daily

CREATE EVENT delete_old_sessions
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM user_sessions WHERE login_time < NOW() - INTERVAL 7 DAY;

Use case: Clean up expired sessions once every 24 hours.

Example 2: Archive Orders Monthly

CREATE EVENT archive_orders
ON SCHEDULE EVERY 1 MONTH
STARTS TIMESTAMP(CURRENT_DATE, '00:00:00')
DO
BEGIN
  INSERT INTO archived_orders
  SELECT * FROM orders WHERE order_date < NOW() - INTERVAL 3 MONTH;
  
  DELETE FROM orders WHERE order_date < NOW() - INTERVAL 3 MONTH;
END;

Use case: Archive old orders and keep the main table lean.

Options for Scheduling

Dropping and Disabling Events

Drop an event:

DROP EVENT event_name;

Temporarily disable:

ALTER EVENT event_name DISABLE;

Enable again:

ALTER EVENT event_name ENABLE;

Best Practices for SQL Events

• Always log or monitor the effects of your events
• Use IF EXISTS and IF NOT EXISTS clauses to avoid errors
• Test events manually before scheduling them
• Avoid long or blocking operations inside events