MySQL triggers apply restrictions to tables when adding, updating, or removing table rows.
Columns in MySQL apply a slight amount of value limitations. For example, setting a column data type as tiny int and not null requires a small number value input. Still, more restrictions are needed to maintain the integrity of data.
This tutorial shows you how to use MySQL triggers and provides examples for each type of trigger.
- A system running MySQL on a database server
- MySQL user account with root privileges
- Knowledge of basic MySQL commands (refer to our downloadable MySQL commands cheat sheet)
What is a Trigger in MySQL?
Triggers allow access to values from the table for comparison purposes using
OLD. The availability of the modifiers depends on the trigger event you use:
Checking or modifying a value when trying to insert data makes the
NEW.<column name> modifier available. This is because a table is updated with new content. In contrast, an
OLD.<column name> value does not exist for an insert statement because there is no information exists in its place beforehand.
When updating a table row, both modifiers are available. There is
OLD.<colum name> data which we want to update to
NEW.<column name> data.
Finally, when removing a row of data, the
OLD.<column name> modifier accesses the removed value. The
NEW.<column name> does not exist because nothing is replacing the old value upon removal.
Note: Manage your databases with ease by learning how to use certain commands to rename a column in MySQL.
MySQL Trigger Example
As an example of an applied trigger, inserting new values into the table person yields a different result than the original input:
Notice the inserted names were initially lowercase. When selecting the table, the first letter shows as capitalized. Even though there is no indication of anything different from a regular insert statement, the trigger fired before the insert statement to capitalize the first letter of the name.
Using MySQL Triggers
Every trigger associated with a table has a unique name and function based on two factors:
AFTER a specific row event.
MySQL triggers fire depending on the activation time and the event for a total of six unique trigger combinations. The before statements help to check data and make changes before making commitments, whereas the after statements commit the data first and then execute statements.
The execution of a set of actions happens automatically, affecting all inserted, deleted, or updated rows in the statement.
CREATE TRIGGER statement syntax to create a new trigger:
CREATE TRIGGER <trigger name> <trigger time > <trigger event> ON <table name> FOR EACH ROW <trigger body>;
The best practice is to name the trigger with the following information:
<trigger time>_<table name>_<trigger event>
For example, if a trigger fires before insert on a table named employee, the best convention is to call the trigger:
Alternatively, a common practice is to use the following format:
<table name>_<first letter of trigger time><first letter of trigger name>
The before insert trigger name for the table employee looks like this:
The trigger executes at a specific time of an event on a table defined by <table name> for each row affected by the function.
To delete a trigger, use the
DROP TRIGGER statement:
DROP TRIGGER <trigger name>;
DROP TRIGGER IF EXISTS <trigger name>;
The error message does not display because there is no trigger, so no warning prints.
Create Example Database
Create a database for the trigger example codes with the following structure:
1. Create a table called person with name and age for columns.
CREATE TABLE person (name varchar(45), age int);
Insert sample data into the table:
INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);
Select the table to see the result:
SELECT * FROM person;
2. Create a table called average_age with a column called average:
CREATE TABLE average_age (average double);
Insert the average age value into the table:
INSERT INTO average_age SELECT AVG(age) FROM person;
Select the table to see the result:
SELECT * FROM average_age;
3. Create a table called person_archive with name, age, and time columns:
CREATE TABLE person_archive ( name varchar(45), age int, time timestamp DEFAULT NOW());
Note: The function
NOW() records the current time. Learn more about date and time functions from our MySQL date functions guide with examples.
Create a BEFORE INSERT Trigger
To create a
BEFORE INSERT trigger, use:
CREATE TRIGGER <trigger name> BEFORE INSERT ON <table name> FOR EACH ROW <trigger body>;
BEFORE INSERT trigger gives control over data modification before committing into a database table. Capitalizing names for consistency, checking the length of an input, or catching faulty inputs with
BEFORE INSERT triggers further provides value limitations before entering new data.
BEFORE INSERT Trigger Example
BEFORE INSERT trigger to check the age value before inserting data into the person table:
delimiter // CREATE TRIGGER person_bi BEFORE INSERT ON person FOR EACH ROW IF NEW.age < 18 THEN SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.'; END IF; // delimiter ;
Inserting data activates the trigger and checks the value of age before committing the information:
INSERT INTO person VALUES ('John', 14);
The console displays the descriptive error message. The data does not insert into the table because of the failed trigger check.
Create an AFTER INSERT Trigger
AFTER INSERT trigger with:
CREATE TRIGGER <trigger name> AFTER INSERT ON <table name> FOR EACH ROW <trigger body>;
AFTER INSERT trigger is useful when the entered row generates a value needed to update another table.
AFTER INSERT Trigger Example
Inserting a new row into the person table does not automatically update the average in the average_age table. Create an
AFTER INSERT trigger on the person table to update the average_age table after insert:
delimiter // CREATE TRIGGER person_ai AFTER INSERT ON person FOR EACH ROW UPDATE average_age SET average = (SELECT AVG(age) FROM person); // delimiter ;
Inserting a new row into the person table activates the trigger:
INSERT INTO person VALUES ('John', 19);
The data successfully commits to the person table and updates the average_age table with the correct average value.
Create a BEFORE UPDATE Trigger
BEFORE UPDATE trigger with:
CREATE TRIGGER <trigger name> BEFORE UPDATE ON <table name> FOR EACH ROW <trigger body>;
BEFORE UPDATE triggers go together with the
BEFORE INSERT triggers. If any restrictions exist before inserting data, the limits should be there before updating as well.
BEFORE UPDATE Trigger Example
If there is an age restriction for the person table before inserting data, the age restriction should also exist before updating information. Without the
BEFORE UPDATE trigger, the age check trigger is easy to avoid. Nothing restricts editing to a faulty value.
BEFORE UPDATE trigger to the person table with the same body as the
BEFORE INSERT trigger:
delimiter // CREATE TRIGGER person_bu BEFORE UPDATE ON person FOR EACH ROW IF NEW.age < 18 THEN SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.'; END IF; // delimiter ;
Updating an existing value activates the trigger check:
UPDATE person SET age = 17 WHERE name = 'John';
Updating the age to a value less than 18 displays the error message, and the information does not update.
Create an AFTER UPDATE Trigger
Use the following code block to create an
AFTER UPDATE trigger:
CREATE TRIGGER <trigger name> AFTER UPDATE ON <table name> FOR EACH ROW <trigger body>;
AFTER UPDATE trigger helps keep track of committed changes to data. Most often, any changes after inserting information also happen after updating data.
AFTER UPDATE Trigger Example
Any successful updates to the age data in the table person should also update the intermediate average value calculated in the average_age table.
AFTER UPDATE trigger to update the average_age table after updating a row in the person table:
delimiter // CREATE TRIGGER person_au AFTER UPDATE ON person FOR EACH ROW UPDATE average_age SET average = (SELECT AVG(age) FROM person); // delimiter ;
Updating existing data changes the value in the person table:
UPDATE person SET age = 21 WHERE name = 'John';
Updating the table person also updates the average in the average_age table.
Create a BEFORE DELETE Trigger
To create a
BEFORE DELETE trigger, use:
CREATE TRIGGER <trigger name> BEFORE DELETE ON <table name> FOR EACH ROW <trigger body>;
BEFORE DELETE trigger is essential for security reasons. If a parent table has any children attached, the trigger helps block deletion and prevents orphaned tables. The trigger also allows archiving data before deletion.
BEFORE DELETE Trigger Example
Archive deleted data by creating a
BEFORE DELETE trigger on the table person and insert the values into the person_archive table:
delimiter // CREATE TRIGGER person_bd BEFORE DELETE ON person FOR EACH ROW INSERT INTO person_archive (name, age) VALUES (OLD.name, OLD.age); // delimiter ;
Deleting data from the table person archives the data into the person_archive table before deleting:
DELETE FROM person WHERE name = 'John';
Inserting the value back into the person table keeps the log of the deleted data in the person_archive table:
INSERT INTO person VALUES ('John', 21);
BEFORE DELETE trigger is useful for logging any table change attempts.
Create an AFTER DELETE Trigger
AFTER DELETE trigger with:
CREATE TRIGGER <trigger name> AFTER DELETE ON <table name> FOR EACH ROW <trigger body>;
AFTER DELETE triggers maintain information updates that require the data row to disappear before making the updates.
AFTER DELETE Trigger Example
AFTER DELETE trigger on the table person to update the average_age table with the new information:
delimiter // CREATE TRIGGER person_ad AFTER DELETE ON person FOR EACH ROW UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); // delimiter ;
Deleting a record from the table person updates the average_age table with the new average:
AFTER DELETE trigger, the information does not update automatically.
Create Multiple Triggers
MySQL does not support having multiple triggers fire at the same time. However, adding multiple logical operations to the same trigger is possible. Use the
END delimiters to indicate the trigger body:
CREATE TRIGGER <trigger name> <trigger time > <trigger event> ON <table name> FOR EACH ROW BEGIN <trigger body>; END;
Make sure to change the default delimiter before creating a trigger with multiple operations.
List all the triggers in a database with:
The output shows a list of all the triggers, including the name and statement contents:
Other information displays as well, such as the creation time and the user who created the trigger.
MySQL triggers provide further validation and control of data before or after specific events happen. Whether you are trying to prevent an error or add restrictions for consistency, triggers help control data input, update, and removal.
Keep in mind the trigger checks happen row-wise, which causes performance to slow down with massive queries. For more materials on this topic, check out our article on how to improve MySQL performance with tuning.