A trigger in MySQL is a set of SQL statements that reside in a system catalog. It is a special type of stored procedure that is invoked automatically in response to an event. Each trigger is associated with a table, which is activated on any DML statement such as INSERT, UPDATE, or DELETE.
A trigger is called a special procedure because it cannot be called directly like a stored procedure. The main difference between the trigger and procedure is that a trigger is called automatically when a data modification event is made against a table. In contrast, a stored procedure must be called explicitly.
Generally, triggers are of two types according to the SQL standard: row-level triggers and statement-level triggers.
Row-Level Trigger: It is a trigger, which is activated for each row by a triggering statement such as insert, update, or delete. For example, if a table has inserted, updated, or deleted multiple rows, the row trigger is fired automatically for each row affected by the insert, update, or delete statement.
Statement-Level Trigger: It is a trigger, which is fired once for each event that occurs on a table regardless of how many rows are inserted, updated, or deleted.
NOTE: We should know that MySQL doesn't support statement-level triggers. It provides supports for row-level triggers only.
Why we need/use triggers in MySQL?
We need/use triggers in MySQL due to the following features:
Limitations of Using Triggers in MySQL
Types of Triggers in MySQL?
We can define the maximum six types of actions or events in the form of triggers:
When we use a statement that does not use INSERT, UPDATE or DELETE query to change the data in a table, the triggers associated with the trigger will not be invoked.
Naming conventions are the set of rules that we follow to give appropriate unique names. It saves our time to keep the work organize and understandable. Therefore, we must use a unique name for each trigger associated with a table. However, it is a good practice to have the same trigger name defined for different tables.
The following naming convention should be used to name the trigger in MySQL:
Trigger Activation Time: BEFORE | AFTER
Trigger Event: INSERT | UPDATE | DELETE
How to create triggers in MySQL?
We can use the CREATE TRIGGER statement for creating a new trigger in MySQL. Below is the syntax of creating a trigger in MySQL: