MySQL – After Insert Trigger

When an insert event occurs on a table, the After Insert Trigger in MySQL is automatically invoked. As a result of this article, we will learn how to create an after insert trigger, along with an example.

Syntax

In MySQL, an AFTER INSERT trigger is created as follows:

CREATE TRIGGER trigger_name   
AFTER INSERT  
ON table_name FOR EACH ROW  
trigger_body ;

Below is an explanation of the AFTER INSERT trigger syntax parameter:

The first step is to specify the name of the trigger that we wish to create. The value should be unique within the schema.
In the second step, we will specify the trigger action time, which should be after the insert clause is invoked.
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.
Finally, we will specify the trigger body, which contains one or more statements that will be executed when the trigger is triggered.

Example

The following example illustrates how to create an AFTER INSERT trigger in MySQL using the CREATE TRIGGER statement.

Assume that we have created a table named “emp_info” as follows:

CREATE TABLE emp_info (
emp_id int NOT NULL,
emp_code varchar(15) DEFAULT NULL,
emp_name varchar(35) DEFAULT NULL,
role varchar(25) DEFAULT NULL,
salary int DEFAULT NULL,
phone varchar(15) DEFAULT NULL,
PRIMARY KEY (emp_id)
)

Insert Data

INSERT INTO emp_info VALUES

(1, 101, “lakshmi”, “JS”, 10000, 1234567890),

(2, 102, “venu”, “Backend”, 15000, 3214567890),

(3, 103, “madhu”, “JS”, 15000, 1234567098),

(4, 104, “sai”, “DB”, 15000, 1236547890),

(5, 105, “tharun”, “DB”, 15000, 4321567890),

(6, 106, “mounika”, “JS”, 10000, 1234098765),

(7, 107, “rani”, “JS”, 10000, 1234567809);

We will create a new table named “emp_detail” in the following manner:

CREATE TABLE emp_detail (
emp_id int NOT NULL,
emp_code varchar(15) DEFAULT NULL,
emp_name varchar(35) DEFAULT NULL,
role varchar(25) DEFAULT NULL,
salary int DEFAULT NULL,
phone varchar(15) DEFAULT NULL,
Lasinserted Time,
PRIMARY KEY (emp_id)
);

Next, we will create an after_insert_details trigger on the emp_info table using a CREATE TRIGGER statement. After an insert operation is performed on the table, this trigger will be triggered.

DELIMITER //
Create Trigger after_insert_details
AFTER INSERT ON emp_info FOR EACH ROW
BEGIN
INSERT INTO emp_detail VALUES (new.emp_id, new.emp_code,
new.emp_name, new.role, new.salary, new.phone, CURTIME());
END

my2

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

INSERT INTO emp_info VALUES
(11, 110, 'Alexandar', 'JS', 13000, '2347346438');

After the update query executes, the student_detail table has been modified. The following SELECT statement can be used to verify this:

SELECT * FROM emp_detail;