Summary: in this tutorial, you will learn about MySQL triggers implementation. In addition, we will show you how MySQL stores the triggers and the limitations of triggers in MySQL.
Introduction to MySQL triggers
In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either before or after the data is changed by INSERT, UPDATE or DELETE statement. Before MySQL version 5.7.2, you can to define maximum six triggers for each table.
BEFORE INSERT
– activated before data is inserted into the table.AFTER INSERT
– activated after data is inserted into the table.BEFORE UPDATE
– activated before data in the table is updated.AFTER UPDATE
– activated after data in the table is updated.BEFORE DELETE
– activated before data is removed from the table.AFTER DELETE
– activated after data is removed from the table.
However, from MySQL version 5.7.2+, you can define multiple triggers for the same trigger event and action time.
When you use a statement that does not use INSERT
, DELETE
or UPDATE
statement to change data in a table, the triggers associated with the table are not invoked. For example, the TRUNCATE statement removes all data of a table but does not invoke the trigger associated with that table.
There are some statements that use the INSERT
statement behind the scenes such as REPLACE statement or LOAD DATA statement. If you use these statements, the corresponding triggers associated with the table are invoked.
You must use a unique name for each trigger associated with a table. However, you can have the same trigger name defined for different tables though it is a good practice.
You should name the triggers using the following naming convention:
(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)
Code language: SQL (Structured Query Language) (sql)
For example, before_order_update
is a trigger invoked before a row in the order
table is updated.
The following naming convention is as good as the one above.
tablename_(BEFORE | AFTER)_(INSERT| UPDATE | DELETE)
Code language: SQL (Structured Query Language) (sql)
For example, order_before_update
is the same as before_order_update
trigger above.
MySQL triggers storage
MySQL stores triggers in a data directory e.g., /data/classicmodels/
with the files named tablename.TRG
and triggername.TRN
:
- The
tablename.TRG
file maps the trigger to the corresponding table. - the
triggername.TRN
file contains the trigger definition.
You can back up the MySQL triggers by copying the trigger files to the backup folder. You can also backup the triggers using the mysqldump tool.
MySQL trigger limitations
MySQL triggers cover all features defined in the standard SQL. However, there are some limitations that you should know before using them in your applications.
MySQL triggers cannot:
- Use
SHOW
,LOAD DATA
,LOAD TABLE
, BACKUP DATABASE,RESTORE
,FLUSH
andRETURN
statements. - Use statements that commit or rollback implicitly or explicitly such as COMMIT , ROLLBACK , START TRANSACTION , LOCK/UNLOCK TABLES , ALTER , CREATE , DROP , RENAME.
- Use prepared statements such as
PREPARE
andEXECUTE
. - Use dynamic SQL statements.
From MySQL version 5.1.4, a trigger can call a stored procedure or stored function, which was a limitation is the previous versions.
In this tutorial, we have shown you how triggers are implemented in MySQL. We also discussed trigger’s storage as well as trigger’s limitations in MySQL.