MySQL Triggers: A Real-World Example

Triggers are useful in MySQL for keeping data consistent and automating work. Here I walk through them with a real scenario: events, the details attached to those events, and tracking which users take part in them.

The scenario

We have an event management system. Events are organized there and each one has details: concepts, outcomes, types, specific days and so on. Every user takes part in certain events, and that is stored in the UserEvents table. The details a user has for each event (concept, outcome, type, specific day) are tracked separately.

The main tables are:

1. Events: the basic information about events.
2. EventDates: the dates of events.
3. DefaultEventsOfUser: the default event dates of users.
4. UserEvents: which user takes part in which event.
5. UserEventDetails: the event details each user has.

1. Add a default date when a new event date is inserted

When a new row is added to EventDates, this trigger adds that event date to every user as a default. It inserts new rows into DefaultEventsOfUser. So when an authorized user adds a new event date, it shows up in all users' events and the data stays consistent.

CREATE TRIGGER add_default_event_date
AFTER INSERT ON EventDates
FOR EACH ROW
BEGIN
    INSERT IGNORE INTO DefaultEventsOfUser (UserId, EventId, Date)
    SELECT DISTINCT UserId, NEW.EventId, NEW.Date
    FROM DefaultEventsOfUser
    WHERE UserId NOT IN (
        SELECT DISTINCT UserId
        FROM DefaultEventsOfUser
        WHERE EventId = NEW.EventId AND Date = NEW.Date
    );
END;

2. Copy event details to the user's own details

When a user's event is added to UserEvents, this trigger copies the general details of the event into the user's own details.

CREATE TRIGGER copy_event_details_to_user_event
AFTER INSERT ON UserEvents
FOR EACH ROW
BEGIN
    INSERT IGNORE INTO UserEventDetails (UserId, EventId, Concept, Achievement, Type, SpecificDay)
    SELECT NEW.UserId, e.EventId, e.Concept, e.Achievement, e.Type, e.SpecificDay
    FROM Events e
    WHERE e.EventId = NEW.EventId;
END;

The trigger copies the details of the newly added user event into UserEventDetails, so users can reach the event details.

3. Clean up user events when an event is deleted

When an event is deleted from Events, this trigger also deletes all user events that belong to it. That keeps the relationships in the database consistent. If we had defined a foreign key between EventsId in Events and the EventsId columns of the other tables, with a cascade rule, we would not need this. I left the relationship out on purpose, to gain performance and speed.

CREATE TRIGGER delete_user_events_on_event_delete
AFTER DELETE ON Events
FOR EACH ROW
BEGIN
    DELETE FROM UserEvents WHERE EventId = OLD.EventId;
    DELETE FROM DefaultEventsOfUser WHERE EventId = OLD.EventId;
END;

4. Remove deleted event dates from the defaults

When a row is deleted from EventDates, this trigger removes it from DefaultEventsOfUser. If we remove the date an event takes place on, the date also has to go from the events every user added by default. This keeps the data consistent.

CREATE TRIGGER remove_default_event_date
AFTER DELETE ON EventDates
FOR EACH ROW
BEGIN
    DELETE FROM DefaultEventsOfUser
    WHERE EventId = OLD.EventId AND Date = OLD.Date;
END;

5. Add new event dates to the defaults

When a new row is added to EventDates, this trigger adds it to DefaultEventsOfUser. It is the opposite of the delete trigger above: a new event is added for all users.

CREATE TRIGGER add_default_event_date
AFTER INSERT ON EventDates
FOR EACH ROW
BEGIN
    INSERT IGNORE INTO DefaultEventsOfUser (UserId, EventId, Date)
    SELECT DISTINCT UserId, NEW.EventId, NEW.Date
    FROM DefaultEventsOfUser
    WHERE UserId NOT IN (
        SELECT DISTINCT UserId
        FROM DefaultEventsOfUser
        WHERE EventId = NEW.EventId AND Date = NEW.Date
    );
END;

Performance

Triggers work well for keeping data consistent and automating operations. They also affect performance. In large databases, a lot of triggers can slow the database down, so plan their use carefully and optimize them.