Triggers & Events in MySQL

In many database applications, certain actions should happen automatically when data changes.

For example:

  • When a new order is inserted, reduce product stock.
  • When a record is deleted, store it in an audit table.
  • Every day at midnight, remove expired sessions.

MySQL provides Triggers and Events to handle such automatic tasks.

1. What Is a Trigger?

A trigger is a special type of stored program that automatically executes when a specific event occurs on a table.

Triggers are activated by:

  • INSERT
  • UPDATE
  • DELETE

They are always associated with a table.

2. When Does a Trigger Execute?

Triggers can execute:

  • BEFORE the event
  • AFTER the event

BEFORE Trigger

Runs before data is inserted, updated, or deleted.

Used for:

  • Validating data
  • Modifying values before saving

AFTER Trigger

Runs after the data change has occurred.

Used for:

  • Logging
  • Auditing
  • Updating related tables

3. Creating a Trigger

Basic syntax:

DELIMITER //

 

CREATE TRIGGER trigger_name

BEFORE INSERT

ON table_name

FOR EACH ROW

BEGIN

    SQL statements;

END //

 

DELIMITER ;

Example:

DELIMITER //

 

CREATE TRIGGER before_insert_student

BEFORE INSERT

ON students

FOR EACH ROW

BEGIN

    SET NEW.created_at = NOW();

END //

 

DELIMITER ;

Here:

  • NEW refers to the new row being inserted.
  • The trigger automatically sets the creation time.

4. Using OLD and NEW Keywords

In triggers:

  • NEW.column_name → Refers to new value (INSERT or UPDATE)
  • OLD.column_name → Refers to old value (UPDATE or DELETE)

Example:

DELIMITER //

 

CREATE TRIGGER after_salary_update

AFTER UPDATE

ON employees

FOR EACH ROW

BEGIN

    INSERT INTO salary_log(emp_id, old_salary, new_salary)

    VALUES (OLD.id, OLD.salary, NEW.salary);

END //

 

DELIMITER ;

This logs salary changes automatically.

5. What Is an Event in MySQL?

An event is a scheduled task that runs automatically at a specified time or interval.

It works like a database-level scheduler.

Before using events, enable the event scheduler:

SET GLOBAL event_scheduler = ON;

6. Creating an Event

Basic syntax:

CREATE EVENT event_name

ON SCHEDULE schedule

DO

SQL statement;

Example:

CREATE EVENT delete_old_records

ON SCHEDULE EVERY 1 DAY

DO

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

This event automatically deletes old log records daily.

7. Triggers vs Stored Procedures

Feature

Trigger

Stored Procedure

Execution

Automatic

Manual (using CALL)

Linked to the table

Yes

No

Used for

Automatic reactions

Reusable logic

Scheduling

No

No (unless called)

Triggers run automatically when table events occur.
Stored procedures must be called explicitly.

8. Triggers vs Events

Feature

Trigger

Event

Activated by

Table change

Schedule

Automatic

Yes

Yes

Use case

Auditing, validation

Scheduled cleanup, automation

Triggers respond to data changes.
Events respond to time-based schedules.

9. Best Practices

  1. Keep triggers simple.
  2. Avoid complex logic inside triggers.
  3. Avoid recursive trigger behavior.
  4. Use events for scheduled maintenance tasks.
  5. Test triggers carefully to avoid unexpected behavior.

10. Real-World Examples

Triggers:

  • Automatically update inventory after order placement.
  • Maintain audit logs.
  • Prevent invalid data entry.

Events:

  • Clear expired sessions.
  • Archive old data.
  • Generate daily reports.

Summary

Triggers:

  • Automatically execute on INSERT, UPDATE, and DELETE.
  • Can run BEFORE or AFTER events.
  • Use OLD and NEW values.

Events:

  • Run automatically on a schedule.
  • Used for periodic database tasks.

Triggers and events help automate database behavior and reduce manual operations in real-world applications.