MySQL BEFORE INSERT Trigger – A Complete Guide

By /
Mysql Before Insert Trigger

Today we will learn how to create a BEFORE INSERT trigger in MySQL along with some examples. We will begin with creating a table and inserting some data into it. Then we will create a trigger to perform actions on that table data. This is going to be a straightforward and beginner-friendly tutorial, so even if you don’t have much prior knowledge of MySQL triggers, you can understand the topic effectively. You can check out the tutorials on MySQL trigger from here.

Introduction and Prerequisites

MySQL BEFORE INSERT is a type of trigger that activates before the insertion of data occurs on the table. The trigger body can check the data before inserting it into the table. Therefore, you can manipulate the data before actually inserting it into the table.

Before heading towards the actual topic, make sure you are logged in the MySQL with a root account or you have privileges associated to trigger operations. Apart from that, you should have a basic understanding of the MySQL command-line interface because we will create and perform operations on triggers using MySQL CLI.

Syntax of BEFORE INSERT Trigger

Following is the basic syntax of the BEFORE INSERT trigger in MySQL.

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name FOR EACH ROW
[trigger_order]
trigger_bodyCode language: SQL (Structured Query Language) (sql)

Where,

  • trigger_name – This can be any name but no two triggers of the same name can reside in the same database.
  • trigger_order – This is the order in which the trigger activates. If two or more triggers are associated with the same table then you can use FOLLOWS and PRECEDES keywords to specify the order in which triggers should activate. However, trigger_order is an optional parameter.
  • trigger_body – The set of operations after trigger activates are written in the trigger body. You can also have multiple SQL statements and those can be written inside the BEGIN…END statement.

Let’s get an example to understand the MySQL BEFORE INSERT trigger.

Examples of MySQL BEFORE INSERT Trigger

First, we will create a table with the fields id, name, marks of subjects out of hundred and the grade. Then we will insert some dummy data into it to check if the table is created successfully and holds the correct data. Using the BEFORE INSERT trigger, we will calculate the percentage of students and insert it in the table.

Creating a Table and Inserting the Data

CREATE TABLE mDetails(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
sub1 INT(3),
sub2 INT(3),
sub3 INT(3),
percentage FLOAT(5,2)
);Code language: SQL (Structured Query Language) (sql)

The table is created successfully, now insert a couple of rows into it.

INSERT INTO mDetails(name,sub1,sub2,sub3)
VALUES("Yuji",91,90,92),("Genos",98,78,89)Code language: SQL (Structured Query Language) (sql)
Create MDetails Table
Creating mDetails Table

We haven’t inserted the values for the percentage column because we will calculate it using the trigger. As of now, the table should hold null values for the percentage column. Let’s display the table data.

SELECT * FROM mDetails;Code language: SQL (Structured Query Language) (sql)
MDetails Table Data
mDetails Table Data

Creating a BEFORE INSERT Trigger

Let’s create a trigger now.

delimiter //
CREATE TRIGGER befIns
BEFORE INSERT
ON mDetails FOR EACH ROW
BEGIN
SET
new.percentage=((new.sub1+new.sub2+new.sub3)*100)/300;
END //Code language: SQL (Structured Query Language) (sql)
Creating Before Insert Trigger
Creating BEFORE INSERT Trigger

In the trigger body, we have calculated the percentage by adding the marks of three subjects and dividing it by three hundred because we have considered that each subject is worth a hundred marks.

You can skip the BEGIN END statements because there is only one statement in the trigger body.

The delimiter is extremely important here. After the trigger is created, make sure you reset the delimiter to a semicolon or whatever you like.

Testing the BEFORE INSERT Trigger

Now let’s test the trigger by inserting the new row in the table.

INSERT INTO mDetails(name,sub1,sub2,sub3)
VALUES("Maya",89,92,93);Code language: SQL (Structured Query Language) (sql)

So we are inserting three marks and if you calculate the percentage for these marks, it should be 91.33%. Let’s check the table data if we are getting the correct data.

SELECT * FROM mDetails;Code language: SQL (Structured Query Language) (sql)
BEFORE INSERT Trigger Result
BEFORE INSERT Trigger Result

As you can see, we have received the expected result.

You can also create and extend this trigger by adding the few statements in the trigger body to calculate the grades based on marks using the IF THEN ELSE statement.

Conclusion

Today we learned how to create a BEFORE INSERT trigger in MySQL using an example. We have taken a very basic example to let you understand the BEFORE INSERT trigger concept in a simple way. You can use this trigger for manipulating the values of other tables as well. You can try creating BEFORE INSERT triggers in multiple ways to understand the topic perfectly. I hope you understood the topic very well. In the next tutorial, we will learn AFTER INSERT trigger.