Triggers

Introduction

So far, every change to a table has happened because you explicitly ran an INSERT, UPDATE, or DELETE statement. But sometimes you want the database itself to react automatically whenever a change happens — without an application having to remember to run extra code every time.

That is exactly what a trigger does. A trigger is a block of SQL that MySQL runs automatically whenever a specific event happens on a specific table. This lesson shows you how to create triggers, when to use BEFORE vs AFTER timing, and how to reference the row being changed.

What is a Trigger?

A trigger is a named block of SQL code that is bound to a table and automatically executes whenever an INSERT, UPDATE, or DELETE happens on that table. You never call a trigger directly — MySQL fires it for you as a side effect of the triggering statement.

Triggers are useful for enforcing rules, keeping data consistent across tables, and automatically recording history — all without relying on your application code to remember to do it every single time.

BEFORE vs AFTER Triggers

Every trigger fires at one of two times relative to the actual data change: BEFORE the row is written, or AFTER it has already been written.

A BEFORE trigger can change the incoming row before it is saved. An AFTER trigger cannot change the row anymore — the change has already happened — but it is the right place to record history or update other tables.

Trigger Syntax

A trigger is created with CREATE TRIGGER, naming the timing (BEFORE/AFTER), the event (INSERT/UPDATE/DELETE), and the table it watches. The FOR EACH ROW clause means the trigger body runs once per affected row.

DELIMITER $$

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements go here
END$$

DELIMITER ;

A table can have several triggers, but MySQL only allows one trigger per combination of timing and event (e.g. only one BEFORE INSERT trigger per table).

OLD and NEW References

Inside a trigger body, MySQL gives you access to the row being changed through two special references: OLD and NEW.

An INSERT trigger only has NEW (there was no previous row). A DELETE trigger only has OLD (there is no new row). An UPDATE trigger has both, so you can compare the before and after values.

-- Example: prevent a salary decrease
CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SET NEW.salary = OLD.salary;
    END IF;
END;

This BEFORE UPDATE trigger compares the incoming NEW.salary against the existing OLD.salary, and if someone tries to lower it, it silently keeps the old value instead.

Worked Example: Audit Logging

A very common real-world use of triggers is keeping an audit trail: whenever a row is deleted from an important table, automatically record what was deleted, and when, into a separate log table.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

CREATE TABLE employees_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    deleted_at DATETIME
);

INSERT INTO employees VALUES
    (1, 'Alice', 55000.00),
    (2, 'Bob', 48000.00),
    (3, 'Carlos', 61000.00);

Now create an AFTER DELETE trigger that copies the deleted row into employees_audit, using OLD to read the values of the row that just disappeared.

DELIMITER $$

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_audit (employee_id, name, salary, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.salary, NOW());
END$$

DELIMITER ;

Now, deleting an employee automatically records what was deleted — no application code required.

DELETE FROM employees WHERE id = 3;

SELECT * FROM employees_audit;

+----------+-------------+---------+---------+---------------------+
| audit_id | employee_id | name    | salary  | deleted_at          |
+----------+-------------+---------+---------+---------------------+
| 1        | 3           | Carlos  | 61000.00| 2026-07-26 10:15:02 |
+----------+-------------+---------+---------+---------------------+

Viewing and Dropping Triggers

You can list all triggers in the current database, or remove one you no longer need.

SHOW TRIGGERS;

SHOW TRIGGERS LIKE 'employees';

DROP TRIGGER after_employee_delete;

SHOW TRIGGERS displays each trigger’s name, its table, the event it watches, and its timing (BEFORE/AFTER), which is helpful when a table has several triggers attached.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Triggers let MySQL react automatically to changes in your data, whether that means validating an incoming value, preventing an invalid update, or recording history into an audit table.

In this lesson, you learned what triggers are, the difference between BEFORE and AFTER timing, how to write a trigger using CREATE TRIGGER, and how OLD and NEW give you access to row data. Next, you will learn about Events — MySQL’s built-in way to run SQL on a schedule.