Menu
Article

MySQL Trigger: A Complete Guide with Examples

Rishabh Pandey

• 2 min read

Introduction

MySQL triggers are powerful database objects that execute automatically in response to specific events in a table. They allow developers to enforce business rules, maintain data integrity, and automate certain database tasks without manual intervention.

In this guide, we'll cover: - What is a MySQL Trigger? - Types of MySQL Triggers - How to Create and Use Triggers in MySQL - Examples of MySQL Triggers - Advantages and Disadvantages of Using Triggers

What is a MySQL Trigger?

A trigger in MySQL is a stored program that automatically executes in response to specific modifications (INSERT, UPDATE, DELETE) on a table. Triggers help maintain the consistency and integrity of the database by automating repetitive actions.

Types of MySQL Triggers

MySQL supports the following types of triggers:

  1. BEFORE INSERT – Executes before an INSERT operation.
  2. AFTER INSERT – Executes after an INSERT operation.
  3. BEFORE UPDATE – Executes before an UPDATE operation.
  4. AFTER UPDATE – Executes after an UPDATE operation.
  5. BEFORE DELETE – Executes before a DELETE operation.
  6. AFTER DELETE – Executes after a DELETE operation.

Creating a MySQL Trigger

Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

Example 1: BEFORE INSERT Trigger

Let's say we have a table employees and we want to ensure that new employees are not assigned a negative salary.

DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;
END;
//
DELIMITER ;

This trigger checks if the new salary is negative and resets it to 0 before the data is inserted.

Example 2: AFTER INSERT Trigger

Assume we have a logs table to store changes when a new employee is added.

DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO logs(action, description, created_at)
    VALUES ('INSERT', CONCAT('New employee added: ', NEW.name), NOW());
END;
//
DELIMITER ;

Dropping a MySQL Trigger

If you need to delete a trigger, use the following syntax:

DROP TRIGGER IF EXISTS trigger_name;

For example:

DROP TRIGGER IF EXISTS before_employee_insert;

Advantages of MySQL Triggers

✅ Automates data integrity checks ✅ Reduces repetitive SQL operations ✅ Enforces business rules at the database level ✅ Improves performance by reducing application logic

Disadvantages of MySQL Triggers

❌ Can make debugging complex ❌ Hidden logic may lead to unexpected behavior ❌ Can impact database performance if overused

Conclusion

MySQL triggers are a powerful feature that enables automation and maintains consistency in your database. While they offer significant benefits, it's essential to use them wisely to avoid performance issues. Experiment with the examples above to integrate triggers effectively into your MySQL database.

Would you like more advanced trigger examples? Let us know in the comments!

Share