MySQL Trigger

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:

• Triggers help us to enforce business rules.
• Triggers help us to validate data even before they are inserted or updated.
• Triggers help us to keep a log of records like maintaining audit trails in tables.
• SQL triggers provide an alternative way to check the integrity of data.
• Triggers provide an alternative way to run the scheduled task.
• Triggers increases the performance of SQL queries because it does not need to compile each time the query is executed.
• Triggers reduce the client-side code that saves time and effort.
• Triggers help us to scale our application across different platforms.
• Triggers are easy to maintain.

Limitations of Using Triggers in MySQL

• MySQL triggers do not allow to use of all validations; they only provide extended validations. For example, we can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints for simple validations.
• Triggers are invoked and executed invisibly from the client application. Therefore, it isn't easy to troubleshoot what happens in the database layer.
• Triggers may increase the overhead of the database server.

Types of Triggers in MySQL?

We can define the maximum six types of actions or events in the form of triggers:

1. Before Insert: It is activated before the insertion of data into the table.
2. After Insert: It is activated after the insertion of data into the table.
3. Before Update: It is activated before the update of data in the table.
4. After Update: It is activated after the update of the data in the table.
5. Before Delete: It is activated before the data is removed from the table.
6. After Delete: It is activated after the deletion of data from the table.

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

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:

(BEFOR | AFTER) table_name (INSERT | UPDATE | DELETE)

Thus,

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:

CREATE TRIGGER trigger_name  
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
         ON table_name FOR EACH ROW  
         BEGIN  
        --variable declarations  
        --trigger code  
        END;