MySQL BEFORE INSERT TRIGGER

Before Insert Trigger in MySQL is invoked automatically whenever an insert operation is executed. In this article, we are going to learn how to create a before insert trigger with its syntax and example.

Syntax

The following is the syntax to create a BEFORE INSERT trigger in MySQL:

CREATE TRIGGER trigger_name 
BEFORE INSERT
ON table_name FOR EACH ROW
Trigger_body ;

The BEFORE INSERT trigger syntax parameter can be explained as below:

First, we will specify the name of the trigger that we want to create. It should be unique within the schema.
Second, we will specify the trigger action time, which should be BEFORE INSERT. This trigger will be invoked before each row modifications occur on the table.
Third, we will specify the name of a table to which the trigger is associated. It must be written after the ON keyword. If we did not specify the table name, a trigger would not exist.
Finally, we will specify the statement for execution when the trigger is activated.

If we want to execute multiple statements, we will use the BEGIN END block that contains a set of queries to define the logic for the trigger. See the below syntax:

DELIMITER $$ 
CREATE TRIGGER trigger_name BEFORE INSERT
ON table_name FOR EACH ROW
BEGIN
   variable declarations
   trigger code
END$$
DELIMITER ;

Restrictions

We can access and change the NEW values only in a BEFORE INSERT trigger.
We cannot access the OLD If we try to access the OLD values, we will get an error because OLD values do not exist.
We cannot create a BEFORE INSERT trigger on a VIEW.

BEFORE INSERT Trigger Example

Let us understand how to create a BEFORE INSERT trigger using the CREATE TRIGGER statement in MySQL with an example.

Suppose we have created a table named employee as follows:

CREATE TABLE employee(
    name varchar(45) NOT NULL,  
    occupation varchar(35) NOT NULL,  
    working_date date,
    working_hours varchar(10)
);

Next, we will insert some records into the employee table and then execute the SELECT statement to see the table data as follows:

Next, we will use a CREATE TRIGGER statement to create a BEFORE INSERT trigger. This trigger is invoked automatically that inserts the occupation = 'Leader' if someone tries to insert the occupation = 'Scientist'.

mysql> DELIMITER //
mysql> Create Trigger before_insert_occupation
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
IF NEW.occupation = 'Scientist' THEN SET NEW.occupation = 'Doctor';
END IF;
END //

If the trigger is created successfully, we will get the output as follows:

How to call the BEFORE INSERT trigger?

We can use the following statements to invoke the above-created trigger:

mysql> INSERT INTO employee VALUES  
('Markus', 'Scientist', '2020-10-08', 14);

mysql> INSERT INTO employee VALUES  
('Alexander', 'Actor', '2020-10-012', 13);

After execution of the above statement, we will get the output as follows:

Execute the SELECT statement to verify the output:

In this output, we can see that on inserting the occupation column values as 'Scientist', the table will automatically fill the 'Doctor' value by invoking a trigger.

How to create BEFORE INSERT Trigger in MySQL workbench?

To create a before insert trigger using this tool, we first need to launch the MySQL Workbench and log in using the username and password we created earlier. We will get the screen as follows:

Now do the following steps for creating BEFORE INSERT trigger:

1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.

2. Select the database (for example, mysqltestdb), double click on it. It will show the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.

3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table (for example, employee), and then click on the Alter Table option. See the below image:

4. Clicking on the Alter Table option gives the screen as below:

5. Now, click on the Trigger tab shown in the previous section's red rectangular box, then select the Timing/Event BEFORE INSERT. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on the trigger based on choosing Timing/Event: