MySQL – Before Insert Trigger

When an insert operation is performed in MySQL, the Before Insert Trigger is automatically invoked. We will learn how to create a before insert trigger in this article.

Syntax:

Here is the syntax for creating a BEFORE INSERT trigger in MySQL:

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

BEFORE INSERT trigger syntax parameter can be explained as follows:

• The first step is to specify the name of the trigger that we wish to create. The value should be unique within the schema.
• As a second step, we will specify the trigger action time, which is BEFORE INSERT. This trigger will be triggered before every row modification on the table occurs.
• Third, we will specify the name of the table associated with the trigger. After the ON keyword, it must be written. A trigger would not exist if the table name was not specified.
• As a final step, we will specify the statement that will be executed when the trigger is triggered.

Restrictions

• The NEW values can only be accessed and modified in a BEFORE INSERT trigger.
• If we attempt to access the OLD values, we will receive an error since they do not exist.
• A BEFORE INSERT trigger cannot be created on a VIEW.

Example

We have created a table named employees as follows:

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

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

INSERT INTO employees VALUES

('sai', 'Scientist', '2020-10-04', 12),
('mounika', 'Engineer', '2020-10-04', 10),
('lashmi', 'Actor', '2020-10-04', 13),
('charan', 'Doctor', '2020-10-04', 14),
('bhanu', 'Teacher', '2020-10-04', 12),
('venu', 'Business', '2020-10-04', 11);
('ravi', 'Scientist', '2020-10-08', 14);
('venkat', 'Actor', '2020-10-12', 14);

Next, we will create a BEFORE INSERT trigger using a CREATE TRIGGER statement. A trigger is invoked automatically that inserts the position= Doctor if someone attempts to insert the position= ‘Scientist’.

DELIMITER //
Create Trigger before_insert
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
IF NEW.position = 'Scientist' THEN SET NEW.position = 'Doctor';
END IF;
END

In the event that the trigger is successfully created.

To invoke the trigger created above, we can use the following statements:

INSERT INTO employees VALUES
('ravi', 'Scientist', '2020-10-08', 14);

Following the execution of the above statement, we will receive the following output: