How To Use MySQL Triggers

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.

Prerequisites

• 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?

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:

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.

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:

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

2. Event. INSERT, UPDATE or DELETE.

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

Alternatively, use:

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

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 ;

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

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 ;

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

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 ;

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

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 ;

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

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 ;

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

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 ;

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

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:

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.