How to use MySQL Before Trigger?

In MySQL, a BEFORE trigger is a database object that you can define to execute automatically before a specified event, such as an UPDATE, INSERT, or DELETE. Triggers permit you to execute custom code or perform certain actions on the basis of specific events that occur within the database.

A BEFORE trigger is executed before the actual operation takes place and can be used to modify data or perform additional validation or calculations. In this article, we will learn how to use MySQL BEFORE trigger.

Syntax and structure of MySQL BEFORE trigger:

triggerName: It is the name that you assign to the trigger.

triggerTime: Indicates that the trigger should be executed before the specified event.

triggerEvent: It specifies the database event that the first trigger should fire.

tableName: It is the name of the table on which the trigger is applied.

FOR EACH ROW: It determines that the trigger should be executed for each affected row.

BEGIN and END: Enclose the trigger body containing one or more SQL statements.

Some points to remember about MySQL BEFORE trigger:

• BEFORE triggers are ideal for enforcing data integrity and performing data validation; that means you can use the first trigger to ensure that specific fields are not null, enforce unique constraints, or validate data formats before allowing any operation.
• Before taking advantage of triggers to log changes made to the table, you can create an audit trail to track and maintain data history by capturing relevant information about changes, such as users, timestamps, and modified values.
• BEFORE triggers could automate complex operations involving multiple tables, and you can use the BEFORE trigger to update related tables or synchronize data across entities on the basis of particular conditions.
• Triggers should be concise, focus on essential tasks, and avoid unnecessary complexity. Complex triggers can hinder performance and maintainability.
• Before you deploy triggers in a production environment, thoroughly test them in a controlled environment to make sure that they work as intended and do not cause any unexpected problems.
• Maintain clear documentation for each trigger, such as its purpose, behavior, and any dependencies. It helps to facilitate the understanding and maintenance of the database.
• Excessive use of triggers can affect database performance, so it is important to optimize them when necessary.

Types of MySQL BEFORE trigger:

There are three kinds of MySQL BEFORE trigger: BEFORE INSERT trigger, BEFORE UPDATE trigger, and BEFORE DELETE trigger.

1. BEFORE INSERT trigger:

In MySQL, a BEFORE INSERT trigger is a database object that permits you to automatically execute actions before an insert operation occurs on a table. It is a kind of database trigger that triggers before the actual insertion of data into a table.

When an INSERT trigger is defined on a table, the trigger is executed before the actual insert occurs. It allows you to modify the data being inserted or perform additional actions based on certain conditions.

Syntax:

In the above example, "triggerName" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is created, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example 1:

Let us create a table called 'train_passengers', which has the fields such as Id, Name, Address, and ticketCharges. Use the given statements to create the table:

The table is created, and it will look like you can see below:

After inserting the values in the 'train_passengers' table, it will look like you can see below:

Now we will create a trigger called 'train_ticket', which will be set to subtract ₹1000 ticket charges from the new ticket charges when a new passenger record is entered in the table. Use the given statements to create a trigger:

After creating a trigger, execute it.

Now, we will insert a new record in the 'train_passengers' table using the given command:

Use the following statement to see the result:

As you can see below, ticket charges of ₹1000 have been deducted from the new charges.

Example 2:

Let us create a table called 'employees', which has fields such as Id, Name, Age, Address, and Income.

Use the given command to create the 'employees' table:

The table is created, and it will look like you can see below:

Now, we will insert the values in the 'employees' table using the given command:

After inserting the values in the 'employees' table, it will look like you can see below:

We will create a trigger called 'income_update', which will be used to update the new employee's salary in the table. If the new employee's salary is less than 30000, then the new employee's salary will be updated to 30000. We are going to use the following statements to create the trigger:

After creating a trigger, execute it.

Now, we will insert a new record to the 'employees' table using the following statements:

After executing the above statement, we will use the following statement to display the 'employees' table:

As you can see below, the income of the new employee added to the table has been updated to 30000.

2. BEFORE UPDATE trigger:

In MySQL, a BEFORE UPDATE trigger is an object in a database that is associated to a specific table and automatically executes actions before an UPDATE operation is performed on that table. It permits you to define custom actions that must be applied before data in the table is modified.

Syntax:

In the above example, "triggerName" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is create, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example 1:

Let us create a table called 'Laptops', which has fields such as Id, ModelNumber, ModelName, Price, Insurance, and Quantity.

Use the given command to create the 'Laptops' table:

The table is created, and it will look like you can see below:

Now, we will insert the values in the 'Laptops' table using the given command:

After inserting the values in the 'Laptops' table, it will appear as shown below:

We will create a trigger called 'Validate', which will be used to update the price of the laptop in the table. We are going to use the following statements to create the trigger:

Now, we will use the following command to update the laptop price:

As you can see, it will show an error as the price is less than 1000.

Now, look at the 'Laptops' table using the following command to see if the price has changed.

As you can see, the price remains the same.

Example 2:

Let us create a table called 'staff', which has fields such as Id, Name, Age, Address, and Wage.

Use the given command to construct the 'staff' table:

The table is created, and it will look like you can see below:

Now, we will insert the values in the 'staff' table using the given command:

After inserting the values in the 'staff' table, it will look like you can see below:

We will create a trigger called 'update_wage', which will be used to update the wage of the staff in the table based on the specific condition. We are going to use the given statements to create the trigger:

Now, we will use the given command to update the staff wage:

Now, look at the 'staff' table using the following command to see if the wage has changed.

As you can see, the wage of Id=2 has been changed.

3. BEFORE DELETE trigger:

In MySQL, a BEFORE DELETE trigger is a database object associated with a table that is designed to automatically execute an action before a delete operation is performed on the table.

Syntax:

In the above example, "triggerName" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is created, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example 1:

Let us create a table called 'customers', which has fields such as Id, Name, Age, Address, and PhoneNumber.

Use the given command to construct the 'customers' table:

The table is created, and it will look like you can see below:

Now, we will insert the values in the 'customers' table using the given command:

After inserting the values in the 'customers' table, it will look like you can see below:

We will create a trigger called 'before_delete' using the given statements:

Now, we will use the given command to delete from the 'customers' table:

As you can see, it shows an error after applying the DELETE trigger.

Now, we will look at the 'customers' table using the given statement:

As you can see below, nothing has been deleted.

Example 2:

Let us create a table called 'labour', which has fields such as Id, Name, Age, Address, and Payment.

Use the given command to construct the 'labour' table:

The table is created, and it will look like you can see below:

Now, we will insert the values in the 'labour' table using the given command:

After inserting the values in the 'labour' table, it will look like you can see below:

We will create a trigger called 'before_delete' using the given statements:

Now, we will use the following command to delete from 'labour' table:

As you can see, it displays an error after applying the DELETE trigger.

Now, we will look at the 'labour' table using the given statement:

As you can see below, nothing has been deleted.

Conclusion:

In this article, we have learned how to use MySQL BEFORE trigger. We have learned that there are three various kinds of BEFORE trigger: BEFORE UPDATE trigger, BEFORE INSERT trigger, and BEFORE DELETE trigger. We have understood each type of BEFORE trigger properly with the help of examples.