MySQL - Triggers

---

---

Generally, a Trigger is defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed automatically (without being called explicitly like regular stored procedures) whenever an event is performed. These events include statements like INSERT, UPDATE and DELETE etc.

To run a MySQL trigger, the user must have admin/superuser privileges.

As per the SQL standard, triggers are usually divided into two categories −

• Row-level Trigger: Triggers that are only executed when each row is either inserted, updated or deleted in a database table. MySQL only supports these type of triggers.

• Statement-level Trigger: Triggers like these are executed on the transaction level, once, no matter how many rows are modified in a table. MySQL does not support these trype of triggers.

Types of Triggers in MySQL

There are six types of row-level triggers in MySQL. They are:

• Before Insert Trigger

• After Insert Trigger

• Before Update Trigger

• After Update Trigger

• Before Delete Trigger

• After Delete Trigger

Before Insert Trigger

The Before Insert Trigger is performed before any value is inserted into the table. Whenever an INSERT statement is executed, the Before Insert trigger goes off, followed by the insertion transaction.

After Insert Trigger

The After Insert Trigger works opposite to the Before Insert Trigger. As implied by its name, it is performed after any value is inserted into the table. Whenever an INSERT statement is executed, the value is inserted into the table first followed by the execution of the trigger.

Before Update Trigger

The Before Update Trigger is performed before any value is updated or modified in the table. Whenever an UPDATE statement is executed, the Before Update trigger goes off, followed by the update transaction.

After Update Trigger

The After Update Trigger works opposite to the Before Update Trigger. As implied by its name, it is performed after any value is updated in the table. Whenever an UPDATE statement is executed, the value is updated in the table first followed by the execution of the trigger.

Before Delete Trigger

The Before Delete Trigger is performed before any value is deleted from the table. Whenever a DELETE statement is executed, the Before Delete trigger goes off, followed by the deletion transaction.

After Delete Trigger

The After Delete Trigger works opposite to the Before Delete Trigger. As implied by its name, it is performed after any value is deleted from the table. Whenever an DELETE statement is executed, the value is deleted from the table first followed by the execution of the trigger.

Advantages of Triggers

Triggers hold a lot of advantages in MySQL database. They are listed as follows −

• Triggers help the database to maintain the integrity of the data stored.

• Triggers are also a means to handle errors from the database layer itself.

• As triggers are invoked automatically without being called explicitly, you don't have to wait for the scheduled events to run.

• Triggers can be useful to track the data changes made in the tables, by logging the events.

• MySQL Triggers can also prevent invalid transactions from being executed.

Disadvantages of Triggers

However, there are disadvantages of using triggers in a MySQL database. Some of them are listed as follows −

• Triggers cannot replace all validations, and only provide extended validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints.

• As triggers are invisible to the client application, it is impossible to understand what goes on in the database layer. Hence, making it difficult to troubleshoot.

• Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high.

• Triggers may increase the overhead of the MySQL Server.

Restrictions on Triggers

Following are some of the restrictions that apply to MySQL triggers −

• One trigger for each event − Each table can have only one trigger for each event combination, i.e. you can't define two same triggers for the same table.

• RETURN statement is not permitted − As triggers don't return any values, the RETURN statement is not permitted.

• Foreign key restriction − Triggers are not activated by foreign key actions.

• Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata.

• Cannot use 'CALL' statement − We cannot use the CALL statement in triggers.

• Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view.

• Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views.