MySQL Triggers: How and why with examples
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 (new.id, 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 (new.id, 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.
BEFOREorAFTER, which controls when the trigger runs relative to the triggering update.- Trigger event, either
INSERT,UPDATE, ORDELETE. Statements of that kind will invoke the trigger. - Name of the table that when updated will cause the trigger to execute.
FOR EACH ROWis 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 (new.id, 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
(new.id,
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:
- Define a new
$table_changestable (e.g.employee_changes). - The change table's schema should include
old_andnew_columns for each column in the main table, as well as achange_timedate. - Define three triggers on the table, one for each of
INSERT,UPDATE, andDELETE. The triggers should insert rows into thechangestable with the rightOLDorNEWvalues.
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.
Validation
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
begin
if
(select count(*) from employees
where upper(concat(first_name, concat(" ", last_name))
= upper(new.full_name)))
= (select 0)
then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'no matching name';
end if;
end;
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 parent.id = new.parent_id;
create trigger refcount_updater_d
after delete on child
for each row
update parent set refCount = refCount - 1
where parent.id = 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_ahas a trigger that updatestable_bandtable_bhas a trigger that updatestable_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 DATAstatements 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.
Conclusion
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.