What is the Difference Between Before and After Trigger in MySQL

The main difference between Before and After trigger in MySQL is that Before trigger performs an action before a certain operation executes on the table while After trigger performs an action after a certain operation executes on the table.

MySQL is a popular DBMS that allows the users to retrieve and manage data in relational databases easily. It stores data in tabular format.  The user can perform various operations on data using Structured Query Language (SQL). Whereas, Trigger is a store program that automatically executes or fires when some kind of an event occurs. The response to the trigger depends on the trigger type.

Key Areas Covered

1. What is Trigger in MySQL
     – Definition, Functionality
2. What is Before Trigger in MySQL
     – Definition, Functionality
3. What is After Trigger in MySQL
     – Definition, Functionality
4. What is the Difference Between Before and After Trigger in MySQL
     – Comparison of Key Differences

Key Terms

After trigger, Before Trigger, MySQL

What is a Trigger in MySQL

A trigger is a set of actions that respond to an operation such as insert, update or delete operation on a specific table. The trigger activates when an SQL operation executes. Moreover, it is possible to create triggers using referential constraints and check constraints that allow applying data integrity rules. Furthermore, trigger helps to update other tables. Also, it automatically generates or converts values for inserted or updated records or performs tasks such as issuing alerts.

What is Before Trigger in MySQL

Before trigger is a trigger that executes before an operation such as insert, update, delete. The user can write these triggers in multiple cases. They help to check or modify values before updating or inserting data in the database.  It is also possible to run other non-database operations coded in user-defined functions.

An example of a trigger before insert operation is as follows.

CREATE TRIGGER trigger_name

BEFORE INSERT

   ON table_name FOR EACH ROW

BEGIN

   — variable declarations

   — trigger code

END;

The trigger_name is used to identify the trigger. It is the name of the trigger to create. The “BEFORE INSERT” statement indicates that the trigger will fire before executing the insert operation. The table_name indicates the name of the table on which the trigger is created. These triggers will update the new values. Old values in the table will remain the same.

What is After Trigger in MySQL

After trigger is a trigger that executes after an operation such as insert, update, delete. The user can write these triggers in multiple cases. He can use them to update data in tables. He can also use it to run non-database operations coded in user-defined functions.

An example of a trigger after insert operation is as follows.

CREATE TRIGGER trigger_name

AFTER INSERT

   ON table_name FOR EACH ROW

BEGIN

   — variable declarations

   — trigger code

END;

The trigger_name is used to identify the trigger. It is the name of the trigger to create. The “AFTER INSERT” statement indicates that the trigger will fire after executing the insert operation. The table_name indicates the name of the table on which the trigger is created. These triggers will update the new values. Old values will remain the same.

Difference Between Before and After Trigger in MySQL

Definition

Before Trigger is a type of trigger that automatically executes before a certain operation occurs on the table. In contrast, after trigger is a type of trigger that automatically executes after a certain operation occurs on the table. Hence, these definitions explain the fundamental difference between before and after trigger in MySQL.

Usage

Usually, the use of Before triggers is to perform validation before accepting data to the table and to check the values before deleting them from the table. But, usually, the use of After triggers is to update data in a table due to an occurred change. Hence, the main difference between before and after trigger in MySQL is where we use them.

Example

In a banking application, before trigger helps to check the values before deleting them while after trigger helps to update the balance in the accounts table.

Conclusion

The main difference between before and after trigger in MySQL is that Before trigger performs an action before a certain operation executes on the table while After trigger performs an action after a certain operation executes on the table.

Reference:

1. “Types of Triggers”, IBM Knowledge Center, Available here.
2. “MySQL: BEFORE INSERT Trigger”, Tech on the Net, Available here.
3. “AFTER INSERT Trigger”, Tech on the Net, Available here.

Image Courtesy:

1. “Database-mysql” By RRZEicons – Own work (CC BY-SA 3.0) via Commons Wikimedia