MySQL Triggers Basics – Part 1

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:

1. Students: Contains the id, name and rank of student.
2. Students_history: Contains the history of the student and their ranks as it changes.

Create and insert statements for the above tables are given below, initially students_history table will be set with no values.

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 about the access of the old value to the trigger in a short while.

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 mysql.triggers system table which is part of the data dictionary.

• tablename.TRG: It maps the trigger to tablename.
• 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 an where clause containing your table name.

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.