How To Use MySQL Triggers

April 12, 2021

Introduction

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.

How To Use MySQL Triggers

Prerequisites

What is a Trigger in MySQL?

A trigger is a named MySQL object that activates when an event occurs in a table. Triggers are a particular type of stored procedure associated with a specific table.

Triggers allow access to values from the table for comparison purposes using NEW and OLD. The availability of the modifiers depends on the trigger event you use:

Trigger EventOLDNEW
INSERTNoYes
UPDATEYesYes
DELETEYesNo

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:

Inserting into a database with a trigger

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:

1. Time. BEFORE or AFTER a specific row event.

2. Event. INSERT, UPDATE or DELETE.

MySQL Triggers

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 Triggers

Use the 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:

before_employee_insert

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:

employee_bi

The trigger executes at a specific time of an event on a table defined by <table name> for each row affected by the function.

Delete Triggers

To delete a trigger, use the DROP TRIGGER statement:

DROP TRIGGER <trigger name>;
Drop trigger command output

Alternatively, use:

DROP TRIGGER IF EXISTS <trigger name>;
Drop trigger error message output

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;
Create table 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;
Create table 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());
Create table person_archive

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>;

The 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

Create a 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 ;
Create before insert trigger

Inserting data activates the trigger and checks the value of age before committing the information:

INSERT INTO person VALUES ('John', 14);
Before insert trigger result

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

Create an AFTER INSERT trigger with:

CREATE TRIGGER <trigger name> AFTER INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

The 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 ;
create after insert trigger

Inserting a new row into the person table activates the trigger:

INSERT INTO person VALUES ('John', 19);
After insert trigger results

The data successfully commits to the person table and updates the average_age table with the correct average value.

Create a BEFORE UPDATE Trigger

Make a BEFORE UPDATE trigger with:

CREATE TRIGGER <trigger name> BEFORE UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

The 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.

Add a 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 ;
Create before update trigger

Updating an existing value activates the trigger check:

UPDATE person SET age = 17 WHERE name = 'John';
before update trigger results

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>;

The 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.

Create an 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 ;
Create after update trigger

Updating existing data changes the value in the person table:

UPDATE person SET age = 21 WHERE name = 'John';
After update trigger results

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>;

The 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 ;
Create before delete trigger

Deleting data from the table person archives the data into the person_archive table before deleting:

DELETE FROM person WHERE name = 'John';
before delete trigger result

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);
person_archive unchanged after insert

The BEFORE DELETE trigger is useful for logging any table change attempts.

Create an AFTER DELETE Trigger

Make an AFTER DELETE trigger with:

CREATE TRIGGER <trigger name> AFTER DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

The AFTER DELETE triggers maintain information updates that require the data row to disappear before making the updates.

AFTER DELETE Trigger Example

Create an 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 ;
Create after delete trigger

Deleting a record from the table person updates the average_age table with the new average:

After delete trigger results

Without the 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 BEGIN and 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.

Show Triggers

List all the triggers in a database with:

SHOW triggers;

The output shows a list of all the triggers, including the name and statement contents:

Output of show triggers

Other information displays as well, such as the creation time and the user who created the trigger.

Conclusion

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.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is an aspiring technical writer at phoenixNAP and a data nerd. Her background in Electrical Engineering and Computing and her teaching experience give her a unique set of skills - being able to easily explain complex technical concepts through her content.
Next you should read
MySQL Stored Procedures
April 13, 2021

MySQL stored procedures group multiple tasks into one and save the task on the server for future use.
Read more
MySQL Data Types
March 4, 2021

MySQL data types are important to understand before you start creating and working with a MySQL database.
Read more
MySQL Commands Cheat Sheet
January 20, 2021

Need a reference sheet for all the important MySQL commands? Check out this MySQL Commands article which…
Read more
How To Create Or Add An Index In MySQL With Examples
August 31, 2020

An index is an integral part of MySQL, helping with organizing and searching information more easily. This…
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.