In this tutorial, we will be looking at what is a trigger, its types, syntax, and the pros and cons. This is going to be a theory-only article and cover everything about MySQL triggers. We will start with the definition and go through its types and syntax. At the last, we will see why should we use triggers and why we shouldn’t. So, let’s get started.
What is MySQL Trigger?
By definition, a trigger is a set of statements or an object that belongs to the database and resides in the system catalog which is activated on a specific event such as INSERT, UPDATE or DELETE.
In simple terms, you can say trigger is a type of stored procedure that is created to perform certain tasks whenever an event occurs on the table data such as insertion, update or deletion of row. These triggers are associated with a table and one table can have multiple triggers.
However, triggers cannot be called directly like stored procedures or stored functions. Triggers get activated automatically whenever the event occurs on the table that the trigger is associated with.
Types of Triggers by SQL standard
There are basically two types of triggers according to SQL standards: row-level and statement-level triggers.
1. Row Level Triggers
Row-level triggers are activated for each row that is going to be modified. For example, if you are updating a hundred rows in the table, the trigger gets activated for hundred times.
2. Statement Level Triggers
Statement level triggers are slightly different from row-level triggers. Regardless of how many rows are going to be modified, the trigger will be activated only once.
Note that, MySQL does not support statement level triggers. It only supports Row Level triggers as of now.
Types of Triggers in MySQL
Triggers respond to the events such as INSERT, DELETE, and UPDATE. However, the response can be either BEFORE or AFTER the event. That’s why there are six types of triggers available in MySQL as follows.
- BEFORE INSERT- This trigger activates before the new row is inserted using INSERT, REPLACE or LOAD DATA statements.
- AFTER INSERT – This trigger activates after the row is inserted in the table.
- BEFORE UPDATE – This trigger activates before the data is updated in the table.
- AFTER UPDATE – This trigger activates after the data is updated in the table.
- BEFORE DELETE – This trigger activates before the data is deleted from the table.
- AFTER DELETE – This trigger activates after the data is deleted from the table.
Note that, statements such as TRUNCATE TABLE and DROP TABLE do not activate the triggers because they do not use the DELETE clause.
Syntax of MySQL Triggers
You can create triggers in MySQL using CREATE TRIGGER statement and it is somewhat similar to creating stored procedures. Following is the syntax to create 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 do not 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 values of the row data can be accessed by the trigger body. To separate the row values that are and will be updated by BEFORE and AFTER statements, use the NEW and OLD keywords.
For example, if you define a trigger that fires on the UPDATE event and modifies a specific value, such as “alias,” you may access the old alias value with OLD.alias and the new alias value with NEW.alias.
Following table shows which events can use the OLD and NEW values.
| Event | OLD | NEW |
| INSERT | No | Yes |
| UPDATE | Yes | Yes |
| DELETE | Yes | No |
Advantages of Triggers
- Helps validating the data even before inserting or updating.
- Provides alternative way to perform a scheduled task.
- Increases performance.
- Helps reducing client side code.
- Easy to maintain.
Disadvantages of Triggers
- Triggers do not support all types of validations.
- Not easy to troubleshoot.
- Triggers might increase server overhead.
Conclusion
In this tutorial, we learned what is a trigger, its types, syntax, and pros and cons. This is an introductory part of triggers and we will cover every type of trigger in the following tutorials as an individual part.
Dividing the trigger into multiple topics will become easier to understand than putting everything in one tutorial, right? So if you want to learn about MySQL triggers in-depth, you may check our next tutorial.