In this article, I will be discussing the basics of using MySQL Triggers on DML statements. We will be learning about creating, managing and implementing multiple triggers for the same action in MySQL.
What Is A MySQL Trigger?
A MySQL trigger is a database object associated with a table and is stored in the database catalog. The associated trigger gets fired when a particular action like an update, insert or delete is executed. Triggers can be used to perform value or error checking or perform additional actions. A trigger can be executed before or after running an INSERT, UPDATE or DELETE statement.
Trigger Events
Triggers are implemented in MySQL using the below 6 Trigger Events :
- Before Update: Trigger activates before data is updated in the table.
- After Update: Trigger activates after data is updated in the table.
- After Insert: Trigger activates after data gets inserted into the table.
- Before Insert: Trigger activates before data gets inserted into the table.
- Before Delete: Triggers activate before data is deleted from the table.
- After Delete: Trigger activates after data is deleted from table.
You can have at maximum 6 triggers per table as you can only have a combination of 6 events as stated above!
Creating MySQL Triggers
Basic Syntax
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN {.... } END;
Trigger name should have the following naming convention:
(BEFORE ] AFTER )_tableName_(INSERT | UPDATE | DELETE)
It essentially describes as to how you want your trigger to get activated. For example, before_sampleTable_insert will activate the trigger before any insert into table sampleTable.
Let us consider two sample tables:
- Students: Contains the id, name
and rank ofstudent . - Students_history: Contains the history of the student and their ranks as it changes.
Create and insert statements for the
CREATE TABLE students ( id int(11) NOT NULL UNIQUE , name varchar(255) NOT NULL, rank int(11) NOT NULL); INSERT INTO students (id, name , rank) VALUES (1,'Joe', 1) ,(2,'Monika',3), (3, 'Phoebe' , 4), (4, 'Chandler',2) ,(5 , 'Rachel', '5') , (6, 'Ross', 6); Create table students_history( id int (11), old_rank int (11), new_rank int (11) );
Creating A Before Update Trigger
Now let us begin creating our very first trigger. We require that whenever the rank of any student is changed we should maintain the history of that record in students_history table. Create statement for a Before Update as given below:
DELIMITER $$ CREATE TRIGGER before_students_update BEFORE UPDATE ON students FOR EACH ROW BEGIN -- trigger body starts INSERT INTO students_history SET id = old.id, old_rank = old.rank, new_rank = new.rank; -- trigger body ends END$$ DELIMITER);
Explanation
We have created a trigger with name before_students_update which will get fired whenever there is an update statement on students table. The action of the MySQL Trigger is defined in the trigger body which states that an insert will be executed into students_history table with the id, old rank, and new rank of the student. A Delimiter is used while defining triggers. The Old keyword refers to the value before it is updated and new keyword refers to the value after it is updated. We will discuss
The FOR EACH ROW in the CREATE TRIGGER statement ensures that the trigger is activated for each row of the table on which an action is being taken upon. If FOR EACH ROW is not defined then the trigger is executed only once. Always include FOR EACH ROW in your create table statement unless you want the trigger to be activated only once
Activating Triggers
In the former sections, we learned about creating a trigger. Now let us see what will actually happen when the trigger gets activated. We execute an update statement on the students table:
mysql> update students set rank = 7 where id = 1 limit 1; mysql> update students set rank = 1 where id = 4 limit 1;
Now on running the above update, trigger before_students_update would have been fired and made an insert with old and new ranks in table students_history. Let us check that
mysql> select * from students_history; +------+----------+----------+ | id | old_rank | new_rank | +------+----------+----------+ | 1 | 1 | 7 | | 4 | 2 | 1 | +------+----------+----------+
So we can see that an insert(s) have been made in table students_history as defined in our trigger body.
Storage Of Triggers
Triggers are stored in
-
tablename .TRG: It maps the trigger totablename . tablename .TRN: This file contains trigger definition.
Earlier we saw triggers making use of old and new keywords, let’s see how they access those values. Basically, two maps are created Trigger.newMap and Trigger.oldMap which contains the values of rows on which triggers are fired.
In MySQL Trigger.oldMap and Trigger.newMap are only available in before update and after update triggers.
Managing Triggers in MySql
Show Triggers: Show triggers statement is used to get information about all the triggers that are stored in the database. If you want to get triggers for a particular table then just add
SHOW TRIGGERS FROM databaseName where table = 'tableName';
Drop Trigger: Drop trigger statement is used to remove or drop an existing trigger.
DROP TRIGGER tableName.triggerName;
Modifying Triggers: Triggers cannot be altered in MySQL. However, it can be done in Oracle.
Advantages And Limitations
Advantages
- Catching errors: Any errors or anomalies occurring in the database layer due to buggy code or business logic can be checked and logged with the help of triggers.
- Logging: Triggers can be used for maintaining a history of the changes made in database tables. We can use them to trace activities of table events.
- Nested Triggers: Triggers can also be nested further witch each other wherein one trigger calls to another. We will be explaining this point in another post.
Limitations
- Slowness: If your system has a high velocity of data, then triggers might slow down your application by becoming overhead for your system, so it is not advisable to use them in this scenario.
- No Values Returned: Unlike stored procedures triggers cannot return any values nor can you pass any parameter to them.
- Hidden from client application: They remain completely hidden from
client application. So if anything was going wrong in Trigger body the client will never come to know about it.
Conclusion
In this article, we learned about implementing triggers. We also discussed the advantages and drawbacks of using MySQL Triggers. We also implemented a before update trigger to explain the basics. Please read about other triggers on my next article.
You can also view other posts related to MySQL on Concatly. You can also read about triggers on the official MySQL Documentation. Kindly leave your valuable comments to help me improve my articles.

Riya is currently working with adda52.com as a Senior Software Engineer. She holds a degree in Computer Science Engineering from MDU. Her interests include adventurous sports and traveling. She loves to code for Web Services, Scripting Language, and SQL.