MySQL trigger example before insert

In this article, we will see how to create the BEFORE INSERT trigger with a practical example.

READ MORE ABOUT TRIGGERS: What are triggers in MySQL

Table of Contents:-

• Syntax of BEFORE INSERT trigger
• Example of BEFORE INSERT trigger

Let us get started by making the sample data. We will be creating two tables, emp_rating and emp_special_bonus.

#create table emp_rating
CREATE TABLE emp_rating
(
    emp_id INT,
    emp_name VARCHAR(255),
    emp_designation VARCHAR(255),
    rating INT
);
#create table emp_special_bonus
 CREATE TABLE emp_special_bonus
(
    emp_id INT,
    emp_name VARCHAR(255),
    special_bonus DECIMAL(10,2)
);

Action Output:-

Syntax of BEFORE INSERT trigger

CREATE TRIGGER name_of_your_trigger
BEFORE INSERT
ON name_of_your_table FOR EACH ROW
trigger_body;

• name_of_your_trigger is the name given to your trigger.
• BEFORE is the action time modifier; that is, the trigger will activate before the event.
• name_of_your_table is the table linked with the trigger. Insertion of a row on this table will cause the trigger to run.
• trigger_body is the code that will be applied when the trigger executes.
• In MySQL Triggers, OLD and NEW keywords are used within the trigger body to distinguish between the columns Before and After the DML execution.

Example of BEFORE INSERT trigger

Create BEFORE INSERT trigger- whenever any insert happens on the table emp_rating, a row should be inserted to the table emp_special_bonus only if the emp_rating is 1. Employee designation will decide the special bonus amount:

Observe the below trigger code for the solution.

 DELIMITER //
CREATE TRIGGER before_insert_emp_rating
 BEFORE INSERT ON emp_rating 	
   FOR EACH ROW BEGIN  
   /* Bonus for Sales Assistant */
	 IF (NEW.rating = 1 AND NEW.emp_designation = "Sales Assistant" )
     THEN 
       INSERT INTO emp_special_bonus ( emp_id,emp_name,special_bonus ) VALUES ( NEW.emp_id, NEW.emp_name,7500);
	ELSEIF
      (NEW.rating = 1 AND NEW.emp_designation = "Sales Manager" )
     THEN 
		INSERT INTO emp_special_bonus ( emp_id,emp_name,special_bonus ) VALUES ( NEW.emp_id, NEW.emp_name,9500);
	ELSEIF ( NEW.rating = 1 AND NEW.emp_designation = "Senior Sales Manager" )
     THEN 
	    INSERT INTO emp_special_bonus ( emp_id,emp_name,special_bonus ) VALUES ( NEW.emp_id, NEW.emp_name,11500);
   END IF;  
 END;//

Action Output:-

Explanation:-

• Here, we are creating a BEFORE INSERT trigger named before_insert_emp_rating on table emp_rating.
• Before inserting a row to the table emp_rating, insert a row to emp_special_bonus only if the rating is 1.
• The emp_designation decides the special_bonus value..

Testing the Trigger:-

Let us now test the trigger by inserting rows into table emp_rating.

# inserting rows to the table emp_rating
INSERT INTO emp_rating (emp_id, emp_name, emp_designation, rating) 
 VALUES(1,"Henry","Sales Manager",1),
 (2,"Richa","Sales Assistant",1),
 (3,"Ved","Sales Assistant",2),
 (4,"George","Senior Sales Manager",1),
 (5,"Alexendra","Sales Assistant",2),
 (6,"Gustav","Sales Manager",3);

Action Output:-

To view the emp_rating table execute select * on it.

SELECT * FROM emp_rating;

Output:-

We will now verify if the trigger was able to insert into the table emp_special_bonus.

SELECT * FROM emp_special_bonus;

Output:-

The output in image_5 shows that the trigger is working correctly. Only the employees with rating 1 were able to make their entries to the emp_special_bonus table.

READ MORE:

• What are triggers in MySQL
• Create trigger on delete
• Trigger with if condition in mysql
• MySQL trigger example after update
• MySQL trigger example before update
• MySQL trigger example after insert

We hope this article helped you to understand how to create BEFORE INSERT triggers in MySQL. Good Luck!!!