How to Use Event Triggers in MariaDB

Galaxy Glossary

How do I schedule automated SQL tasks with event triggers in MariaDB?

Event triggers (events) let MariaDB run SQL automatically at scheduled times without external cron jobs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does an event trigger do in MariaDB?

An event trigger (simply “event”) stores a SQL statement and a schedule. When the schedule fires, the server executes the statement automatically, enabling periodic cleanup, summary tables, or report generation without external schedulers.

How do I enable the event scheduler?

Run SET GLOBAL event_scheduler = ON; or start MariaDB with --event-scheduler=ON.Without this switch, no events run even if they exist.

What is the basic CREATE EVENT syntax?

Use CREATE EVENT followed by a schedule: ON SCHEDULE AT for a one-off task, or EVERY ... STARTS ... for recurring tasks. Add ON COMPLETION PRESERVE to keep one-off events after they run.

How do I create a daily cleanup job?

Create an event on the Orders table that archives completed orders older than a year.The example below shows a nightly run at 02:00.

Can I alter, disable, or drop an existing event?

Use ALTER EVENT to change schedules or body, ALTER EVENT ... ENABLE | DISABLE to toggle execution, and DROP EVENT to remove it entirely.

Best practices for event triggers

Keep event bodies idempotent so reruns cause no harm. Use qualified table names and LIMIT to avoid long locks.Monitor the information_schema.EVENTS table for status and last execution time.

Common mistakes and fixes

Why didn’t my event run?

Most often the global event_scheduler is OFF. Enable it and re-check the next execution timestamp in SHOW EVENTS.

Why does my event fail with a privilege error?

The event executes with the DEFINER user’s rights. Ensure that user still exists and has the correct permissions on referenced tables.

Why How to Use Event Triggers in MariaDB is important

How to Use Event Triggers in MariaDB Example Usage


-- Disable stock for products that have been out of stock for 30 days
CREATE EVENT ev_mark_unavailable
    ON SCHEDULE EVERY 1 DAY STARTS '2024-06-08 01:00:00'
    DO
      UPDATE Products
      SET stock = 0
      WHERE stock = 0
        AND DATEDIFF(CURDATE(), (SELECT MAX(order_date)
                                 FROM Orders o
                                 JOIN OrderItems oi ON o.id = oi.order_id
                                 WHERE oi.product_id = Products.id)) > 30;

How to Use Event Triggers in MariaDB Syntax


CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule_clause
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'description']
    DO sql_statement;

schedule_clause:
    AT timestamp_value
  | EVERY interval [STARTS timestamp] [ENDS timestamp]

-- Ecommerce example: run nightly cleanup
CREATE EVENT IF NOT EXISTS ev_archive_orders
    ON SCHEDULE EVERY 1 DAY STARTS '2024-06-08 02:00:00'
    DO
      DELETE FROM Orders
      WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Common Mistakes

Mistake: Leaving the global event_scheduler OFF. Result: Events never execute. Fix: Run SET GLOBAL event_scheduler = ON; or set --event-scheduler=ON in my.cnf.
Mistake: Forgetting ON COMPLETION PRESERVE for one-off events. Result: The event disappears after first run, making auditing impossible. Fix: Add ON COMPLETION PRESERVE to keep the definition for review or rerun.