In this tutorial, we will learn what a trigger is, how to create MySQL triggers, and go through some easy and effective examples. We will start from the basics and then will see the trigger syntax, types of triggers, and an example. This tutorial will cover everything from basics to advance, so even if you are a beginner and don’t know anything about triggers, you will understand the topic very well at the end.
Recommended read: Introduction to MySQL Triggers – Definition, Types, and Syntax
What is Trigger?
A trigger is a set of instructions which is also called an object that is associated with a table. Think of a trigger as a real-life trigger. A real-life trigger invokes some action when you pull/push it. In the database, a trigger functions the same way. It activates when a particular event occurs for a specific table. The events can be anything like insert, update, or delete. Overall, when you perform insert, update or delete operations on a table, the event occurs. That event leads to activating the trigger that performs the certain tasks you specify in the trigger body.
Enough theory – let’s see the syntax of defining trigger now.
Syntax to Create MySQL Trigger
The syntax to create a MySQL trigger is as simple as creating a table. You can create triggers using CREATE TRIGGER statement. Following is the complete syntax for creating triggers.
CREATE
TRIGGER [IF NOT EXISTS] trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
[trigger_order]
trigger_bodyCode language: SQL (Structured Query Language) (sql)Where,
- CREATE TRIGGER – statement to create trigger
- IF NOT EXISTS – This statement prevents an error if the trigger with the same name is already present in same database schema.
- trigger_name – Any trigger name you specify. No two triggers with same name can be created for same database.
- trigger_time – This is the time that specifies when to activate the trigger. It can be BEFORE or AFTER that indicates if the trigger will activate before or after each row to be modified.
- trigger_event – Trigger event indicates what operation will activate the trigger. Following is the list of trigger events that are allowed in the MySQL.
- INSERT – When insert operation occurs (new row gets inserted), the trigger activates. Note that, triggers can be activated by INSERT, LOAD DATA and REPLACE statements as well.
- UPDATE – Whenever a row gets updated, the trigger activates.
- DELETE – Whenever a row is deleted using DELETE or REPLACE statements, the trigger activates. Note that, TRUNCATE TABLE and DROP TABLE statements do not activates the trigger because they donot use DELETE statement.
- table_name – Name of the table you want to create the trigger for.
- trigger_order – You can have multiple triggers on the same table. However, triggers activate in the order they were created. If you want to activate the trigger in particular order, you can specify using the FOLLOWS and PRECEDES statement. To activate the new trigger before exisiting one, use PRECEDES; whereas FOLLOWS clause is used to activate the new trigger after exisiting trigger.
- trigger_body – The set of operations to perform after trigger activates are grouped together in trigger_body. To execute multiple statements, you can use BEGIN and END statements.
The trigger_body can access the values of the row data. You can use NEW and OLD keywords to distinguish the row values that are and will be modified by BEFORE and AFTER statements.
For example, if you specify the trigger that activates on the UPDATE event and updates a particular value, say “alias”, you can use OLD.alias to get the old alias value and NEW.alias to get the new alias value.
Following table shows which events can use the OLD and NEW values.
| Event | OLD | NEW |
| INSERT | No | Yes |
| UPDATE | Yes | Yes |
| DELETE | Yes | No |
Examples of MySQL Triggers
Before creating triggers, we are going to create the table with fields id, name, age, city, phone number, email, education, and last updated date. After that, we will insert data into and create triggers of different types.
CREATE TABLE studs(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
gender VARCHAR(10),
age INT(2),
city VARCHAR(100),
phone VARCHAR(10),
email VARCHAR(100),
education VARCHAR(50),
last_updated DATETIME
);Code language: SQL (Structured Query Language) (sql)INSERT INTO studs(name,gender,age,city,phone,email,education,last_updated)
values("Yuji","male",21,"London","9878677654","yuji@gmail.com","B.Tech",NOW()),
("Genos","male",20,"New York","7687653524","Genos@gmail.com","BE",NOW()),("Raj","male",22,"Mumbai","9987256738","Raj@gmail.com","B.Arch",NOW()),
("Maya","male",21,"Delhi","8878986756","Maya@gmail.com","MPhil",NOW());Code language: SQL (Structured Query Language) (sql)Let’s see if the data is inserted successfully in the table.
SELECT * FROM studs;Code language: SQL (Structured Query Language) (sql)
As you can see, the data has been inserted successfully with the current date and time.
We will now create a BEFORE INSERT trigger in which we will add the Mr or Ms before the name of students according to their gender.
We need IF-ELSE conditional statement to get the right abbreviation for the student. If the gender is male, we will update the name with Mr.name and the same if the student is female.
DELIMITER //
CREATE TRIGGER findAbbr
BEFORE INSERT ON studs FOR EACH ROW
BEGIN
IF NEW.gender = "male" THEN
SET NEW.name = CONCAT("Mr. ",NEW.name);
ELSEIF NEW.gender = "female" THEN
SET NEW.name = CONCAT("Ms. ",NEW.name);
ELSE
SET NEW.name = NEW.name;
END IF;
END
//Code language: SQL (Structured Query Language) (sql)
Here, we have used the IF-ELSEIF statement that checks if the gender is male or female and accordingly updates the new name with the concatenated name. If the gender is neither male nor female, the trigger will keep the original name as it is.
Let’s insert a new row into the table and see if the trigger works fine.
INSERT INTO studs(name,gender,age,city,phone,email,education,last_updated)
VALUES("May","female",20,"Paris","8875629837","may@gmail.com","B.A", NOW());Code language: SQL (Structured Query Language) (sql)
The row was inserted successfully. Let’s check the table. We should get the name of a new row as Ms. May.
SELECT * FROM studs;Code language: SQL (Structured Query Language) (sql)
As you can see, the trigger worked the way it should, and we got the expected output.
Conclusion
Today we learned how to create a trigger in MySQL using CREATE TRIGGER statement. MySQL Trigger is a vast topic, and you can have multiple sub-topics under it. In this tutorial, we have seen an example of MySQL BEFORE INSERT trigger. However, you can create any type of trigger using the same concept. Stay connected with us for more tutorials on triggers. See you in the following tutorial!
References
MySQL official documentation on trigger syntax and examples.
MySQL official documentation on creating trigger statements.