MySQL Triggers: How and why with examples

8 min read

MySQL allows you to define triggers on your tables that will do additional work or validation every time data in the table is changed. This tutorial will teach you how to use triggers and give you some ideas about what they're good for.

What's a trigger?

A trigger is a set of SQL statements that run every time a row is inserted, updated, or deleted in a table. Here's a simple classic example using MySQL trigger syntax: defining a trigger that logs updates to one table by inserting into another.

mysql> create table employees (
    id bigint primary key auto_increment, 
    first_name varchar(100), 
    last_name varchar(100));
mysql> create table hiring (emp_id bigint, hire_date timestamp);
mysql> create trigger hire_log after insert on employees 
    for each row insert into hiring values (, current_time());
mysql> insert into employees (first_name, last_name) values ("Tim", "Sehn");
mysql> select * from hiring;
| emp_id | hire_date           |
|      1 | 2023-06-08 12:21:27 |
1 row in set (0.00 sec)

This trigger inserts a new row into the hiring table every time a row is inserted into the employees table.

Let's break down the definition of a trigger and see what each part does.

Defining a trigger

The trigger we defined above looks like this. I'm commenting every element in the definition to make it clear.

create trigger 
    hire_log -- the name of the trigger
    after -- before or after the change
    insert -- which kind of change, (insert, update, or delete)
    on employees -- the name of the table to watch for changes
    for each row -- boilerplate to begin the trigger body
    insert into hiring values (, current_time()) -- trigger body

So a trigger definition has a number of parts that must be declared in the order above. They are:

  • The name of the trigger. Trigger names must be unique in a schema.
  • BEFORE or AFTER, which controls when the trigger runs relative to the triggering update.
  • Trigger event, either INSERT, UPDATE, OR DELETE. Statements of that kind will invoke the trigger.
  • Name of the table that when updated will cause the trigger to execute.
  • FOR EACH ROW is just boilerplate syntax you must include.

Finally, the last part of the trigger definition is the SQL statement you want to execute. It can be almost anything, with some caveats (see following sections).

NEW and OLD values in triggers

You might have noticed in our example above that the trigger body references a table called NEW:

insert into hiring values (, current_time())

Trigger bodies let you reference the implicit table aliases NEW and OLD to refer to values on the row being inserted, updated, or deleted. INSERT triggers can only reference NEW, and DELETE triggers can only reference OLD. UPDATE triggers can reference both.

Let's create a different trigger to track name changes in our employees.

mysql> create table name_changes (
    emp_id bigint, 
    old_name varchar(200), 
    new_name varchar(200),
    change_time timestamp default (current_timestamp())

mysql> create trigger name_change_trigger 
    after update on employees 
    for each row 
    insert into name_changes (emp_id, old_name, new_name) values 
    concat(old.first_name, concat(" ", old.last_name)), 
    concat(new.first_name, concat(" ", new.last_name)));
mysql> update employees set last_name = "Holmes" where first_name = "Tim";
mysql> select * from name_changes;
| emp_id | old_name | new_name   | change_time         |
|      1 | Tim Sehn | Tim Holmes | 2023-06-09 08:36:54 |
1 row in set (0.00 sec)

This is another common pattern to create a low-effort transactional log of changes in your database to help debug data problems down the road.

What are triggers used for?

So now that you understand how to define a trigger in MySQL, you might be asking yourself why you would want to. What are they good for? What do people use these things to do? Triggers are a general tool you can use to do all sorts of things, but here are some of the more common ones in our experience.

Change logs

This is what the above examples do. If you want a durable record of how data in your tables change, then for each table you want to track in this way you do this:

  1. Define a new $table_changes table (e.g. employee_changes).
  2. The change table's schema should include old_ and new_ columns for each column in the main table, as well as a change_time date.
  3. Define three triggers on the table, one for each of INSERT, UPDATE, and DELETE. The triggers should insert rows into the changes table with the right OLD or NEW values.

This is a great pattern when your application doesn't need to inspect historical values, but you just want an audit log for when something goes wrong. The best part is that since the logic for this change log is defined in the database, there's no way for an application developer to screw it up or get around it, it just happens automatically. This is an implementation of a broader soft delete pattern.


Another common use case for triggers is validating data in tables. Triggers offer a lot more flexibility than constraints here.

For example, let's say you want to make sure that a table uses only upper-case letters. You could do that with a CHECK constraint, like this:

mysql> create table employees2 (
    id bigint primary key auto_increment, 
    first_name varchar(100), 
    last_name varchar(100),
    check (upper(first_name) = first_name),
    check (upper(last_name) = last_name)

But you could also make a trigger to do this automatically, which is really handy when you have a database being written by lots of different places that are challenging to track down. These two triggers do the same thing as the check constraint, but by automatically correcting the values instead of preventing updates:

mysql> create trigger upper_name_i 
    before insert on employees 
    for each row 
    set new.first_name = upper(new.first_name), 
    new.last_name = upper(new.last_name);

mysql> create trigger upper_name_u 
    before update on employees 
    for each row 
    set new.first_name = upper(new.first_name), 
    new.last_name = upper(new.last_name);

Now when we insert lowercase values into the table, they get uppercased automatically:

mysql> insert into employees (first_name, last_name) values ('aaron', 'son');
Query OK, 1 row affected (0.02 sec)

mysql> select * from employees where first_name = 'AARON';
| id | first_name | last_name |
|  2 | AARON      | SON       |

It's also possible to use triggers to express constraints that aren't possible any other way, such as those involving subqueries. In this case, you can use the signal keyword, which is MySQL's way of throwing an exception. For these advanced constraints you'll need to use the BEGIN .. END syntax to run multiple statements and conditional logic.

For example, here's a trigger that enforces that a full_name field in one table matches the separate first_name and last_name fields in another in a case-insensitive fashion:

mysql> create table birthdays (
    full_name varchar(200), 
    birthday date);

mysql> create trigger full_name_check 
    before insert on birthdays
    for each row
            (select count(*) from employees 
                where upper(concat(first_name, concat(" ", last_name)) 
                    = upper(new.full_name)))
            = (select 0)
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'no matching name';
        end if;
mysql> insert into birthdays values ("Tim Sehn", '2020-02-01');
ERROR 1644 (45000): no matching name
mysql> insert into birthdays values ("Tim Holmes", '2020-02-01');
Query OK, 1 row affected (0.01 sec)

Yes this is a silly way to structure these tables to make this trigger necessary. But of course, we don't always get to work on well thought-out database schemas we designed ourselves, so it's useful to have tricks up our sleeves.

Note that defining SQL scripts with BEGIN .. END syntax requires special syntax if you're using the MySQL shell.

For hopefully obvious reasons, triggers used for validation should always fire BEFORE the table is updated.

Table dependencies

The ability to alter other tables is the biggest benefit of triggers that you can't get from other kinds of constraints. You can use this ability to implement all sorts of exotic inter-table dependencies that would be impossible otherwise.

For example, it's possible to define a foreign key constraint with built-in reference counting, so that you can see the cardinality of table keys at a glance without an expensive second index scan.

Define the parent and child tables like so:

create table parent(
    id int primary key,
    refCount int
create table child(
    id int primary key,
    parent_id int

alter table child add foreign key (parent_id) references parent(id);

Now define a trigger that updates the refcount whenever we insert or delete into the child table:

create trigger refcount_updater_i
    after insert on child
    for each row
    update parent set refCount = refCount + 1
        where = new.parent_id;

create trigger refcount_updater_d
    after delete on child
    for each row
    update parent set refCount = refCount - 1
        where = old.parent_id;

Now when we insert into the tables, the refcount updates itself:

mysql> insert into parent (id) values (1), (2), (3);
mysql> insert into child values (1,1), (2,2), (3,2), (4,3), (5,3), (6,3);

mysql> select * from parent order by 1;
| id | refCount |
|  1 |        1 |
|  2 |        2 |
|  3 |        3 |

mysql> delete from child where id = 6;
mysql> select * from parent order by 1;
| id | refCount |
|  1 |        1 |
|  2 |        2 |
|  3 |        2 |

mysql> delete from child;
mysql> select * from parent order by 1;
| id | refCount |
|  1 |        0 |
|  2 |        0 |
|  3 |        0 |

Advanced topics

There are some esoteric rules you have to follow when defining your triggers:

  • Your trigger can't update the table it's defined on
  • You can't define triggers in a circular fashion so that table_a has a trigger that updates table_b and table_b has a trigger that updates table_a. You can chain them together though, just as long as there are no cycles.
  • Triggers have an order they run in which you can specify with keywords. We didn't cover that here, but if you define more than one trigger on a table you should know about it.
  • You can't use DDL and certain other statements in trigger bodies. Stored procedures work fine though.

There are also some caveats to be aware of:

  • Triggers only fire with UPDATE, INSERT, etc. statements. They won't fire on e.g. LOAD DATA statements or other ways of getting data into tables.
  • Triggers don't fire on replicas, but their effects in terms of the rows they change are replicated like any other row changes. This means if you have non-data side effects for triggers, they won't faithfully execute on a replica.


This tutorial should get you on your way using triggers in MySQL. They're pretty handy!

Before you go, did we mention that we built a MySQL-compatible database with built-in version control called Dolt? Dolt is a great way to experiment with advanced database features like triggers, because you have the safety of version control to fall back on -- if you mess up, you can always dolt reset --hard to roll back to a previous revision. Dolt also lets you diff two revisions of your database, so you can see what rows a trigger changed, as well as compare revisions of a trigger definition to look for bugs.

Dolt is free and open source, so go check it out! All of the examples in this tutorial work in Dolt's built-in SQL shell, so you can use it to follow along at home.

Have questions about Dolt or MySQL triggers? Join us on Discord to talk to our engineering team and meet other Dolt users.



Get started with Dolt

Or join our mailing list to get product updates.