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.
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.
- 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.
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: