Trigger Overview
Contents
A trigger, as its name suggests, is a set of statements that run, or are triggered, when an event occurs on a table.
The event can be an INSERT, and UPDATE or a DELETE.
Creating a trigger
Here's a simple example to demonstrate a trigger in action. Using these two tables as an example:
MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.29 sec) CREATE TABLE animal_count (animals int); INSERT INTO animal_count (animals) VALUES(0);
We want to increment a counter each time a new animal is added. Here's what the trigger will look like:
MariaDB [test]> CREATE TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
The trigger has:
- a name (in this case
increment_animal
) - a trigger time (in this case after the specified trigger event)
- a trigger event (an
INSERT
) - a table with which it is associated (
animals
) - a set of statements to run (here, just the one UPDATE statement)
AFTER INSERT
specifies that the trigger will run after an INSERT
. The trigger could also be set to run before, and the statement causing the trigger could be a DELETE
or an UPDATE
as well.
Now, if we insert a record into the animals
table, the trigger will run, incrementing the animal_count table;
MariaDB [test]> SELECT * FROM animal_count; +---------+ | animals | +---------+ | 0 | +---------+ MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark'); MariaDB [test]> INSERT INTO animals (name) VALUES('baboon'); MariaDB [test]> SELECT * FROM animal_count; +---------+ | animals | +---------+ | 2 | +---------+
For more details on the syntax, see CREATE TRIGGER.
Dropping triggers
To drop a trigger, use the DROP TRIGGER statement. Triggers are also dropped if the table with which they are associated is also dropped.
MariaDB [test]> DROP TRIGGER increment_animal;
Triggers metadata
The Information Schema TRIGGERS Table stores information about triggers.
The SHOW TRIGGERS statement returns similar information.
The SHOW CREATE TRIGGER statement returns a CREATE TRIGGER statement that creates the given trigger.
More complex triggers
Triggers can consist of multiple statements enclosed by a BEGIN and END. If you're entering multiple statements on the command line, you'll want to temporarily set a new delimiter so that you can use a semicolon to delimit the statements inside your trigger. See Delimiters in the mysql client for more.
MariaDB [test]> DROP TABLE animals; MariaDB [test]> UPDATE animal_count SET animals=0; MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; MariaDB [test]> DELIMITER // CREATE TRIGGER the_mooses_are_loose AFTER INSERT ON animals FOR EACH ROW BEGIN IF NEW.name = 'Moose' THEN UPDATE animal_count SET animal_count.animals = animal_count.animals+100; ELSE UPDATE animal_count SET animal_count.animals = animal_count.animals+1; END IF; END; // MariaDB [test]> DELIMITER ; MariaDB [test]> INSERT INTO animals (name) VALUES('Aardvark'); MariaDB [test]> SELECT * FROM animal_count; +---------+ | animals | +---------+ | 1 | +---------+ MariaDB [test]> INSERT INTO animals (name) VALUES('Moose'); MariaDB [test]> SELECT * FROM animal_count; +---------+ | animals | +---------+ | 101 | +---------+
Trigger errors
If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, and it is an AFTER trigger, the trigger will not run, but the original statement will.
Here, we'll drop the above examples, and then recreate the trigger with an error, a field that doesn't exist, first using the default InnoDB, a transactional engine, and then again using MyISAM, a non-transactional engine.
MariaDB [test]> DROP TABLE animals; MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; MariaDB [test]> CREATE TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.id = animal_count_id+1; MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark'); ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list' MariaDB [test]> SELECT * FROM animals; Empty set (0.00 sec)
And now the identical procedure, but with a MyISAM table.
MariaDB [test]> DROP TABLE animals; MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM; MariaDB [test]> CREATE TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.id = animal_count_id+1; MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark'); ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list' MariaDB [test]> SELECT * FROM animals; +----+----------+ | id | name | +----+----------+ | 1 | aardvark | +----+----------+ 1 row in set (0.00 sec)
The following example shows how to use a trigger to validate data. The SIGNAL statement is used to intentionally produce an error if the email field is not a valid email. As the example shows, in that case the new row is not inserted (because it is a BEFORE trigger).
CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name CHAR(20), last_name CHAR(20), email CHAR(100) ) ENGINE = MyISAM; DELIMITER // CREATE TRIGGER bi_user BEFORE INSERT ON user FOR EACH ROW BEGIN IF NEW.email NOT LIKE '_%@_%.__%' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email field is not valid'; END IF; END; // DELIMITER ; INSERT INTO user (first_name, last_name, email) VALUES ('John', 'Doe', 'john_doe.example.net'); ERROR 1644 (45000): Email field is not valid SELECT * FROM user; Empty set (0.00 sec)