MySQL trigger example after insert

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

Table of Contents:-

• Syntax of AFTER INSERT trigger
• Example of AFTER INSERT trigger

READ MORE ABOUT TRIGGERS: What are triggers in MySQL

Let us get started by making the sample data. We will be creating two tables sale_details and total_sales.

#create the table sale_details
CREATE TABLE sale_details (
    id INT ,
    sale_person_id VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255)
);
#create the table total_sales
CREATE TABLE total_sales (
    id INT AUTO_INCREMENT,
    sales_department  VARCHAR(255) ,
    total_sale INT,
    PRIMARY KEY(id)
);

Action Output:-

Tables got created, and we will be using them in our example.

Syntax of AFTER INSERT trigger

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

• name_of_your_trigger is the name given to your trigger.
• AFTER is the action time modifier that is trigger will activate after the event.
• name_of_your_table is the table linked with the trigger. Insertion of a row from 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 AFTER INSERT trigger

Create the AFTER INSERT trigger; whenever any insert happens on the table sale_details, the table total_sales should either insert or update the total sales for every department to maintain the total number of products sold per department.

Observe the below trigger code for the solution.

 DELIMITER //
CREATE TRIGGER after_insert_sale_details
 AFTER INSERT ON sale_details 
 FOR EACH ROW BEGIN  
   IF(NEW.sales_department = "Kitchen Essentials")
   THEN
    INSERT INTO total_sales (id, sales_department, total_sale) VALUES(1,NEW.sales_department, NEW.no_products_sold) 
    ON DUPLICATE KEY UPDATE total_sale = total_sale + NEW.no_products_sold, sales_department = NEW.sales_department;
 ELSEIF
	(NEW.sales_department = "Apparels")
   THEN
    INSERT INTO total_sales (id, sales_department, total_sale) VALUES(2,NEW.sales_department, NEW.no_products_sold) 
    ON DUPLICATE KEY UPDATE total_sale = total_sale + NEW.no_products_sold, sales_department = NEW.sales_department;
 ELSEIF
	(NEW.sales_department = "Medicines")
   THEN
    INSERT INTO total_sales (id, sales_department, total_sale) VALUES(3,NEW.sales_department, NEW.no_products_sold) 
    ON DUPLICATE KEY UPDATE total_sale = total_sale + NEW.no_products_sold, sales_department = NEW.sales_department;
   END IF;
END;//

Explanation:-

• Here, we are creating AFTER INSERT trigger named after_insert_sale_details on table sale_details.
• If the insert happens on the table sale_details for the first time, a row gets inserted in the total_sales table.
• When there are successive inserts in sale_details for the same department, the total_sales table will update by adding the NEW no_products_sold to maintain the total sale for a particular department.

Testing the Trigger:-

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

INSERT INTO sale_details (id, sale_person_id, sale_person_name, no_products_sold, sales_department) 
 VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"),
 (2,"sd1","Henry",5000,"Apparels"),
 (3,"sd1","Henry",40,"Medicines"),
 (4,"sd2","Richa",3000,"Kitchen Essentials"),
 (5,"sd2","Richa",500,"Apparels"),
 (6,"sd2","Richa",50,"Medicines"),
 (7,"sd3","Ved",100,"Kitchen Essentials"),
 (8,"sd3","Ved",150,"Apparels"),
 (9,"sd3","Ved",1000,"Medicines");

To view the sale_details table execute select * on it.

SELECT * FROM sale_details;

We will now verify if the total_sales table has the sum of no_products_sold for each department.

SELECT * FROM total_sales;

Output:-

The output in image_3 shows that the trigger is working correctly. We can re-verify the same by inserting some more rows to the sale_details table.

INSERT INTO sale_details (id, sale_person_id, sale_person_name, no_products_sold, sales_department) 
 VALUES(10,"sd4","Mounika",200,"Kitchen Essentials"),
 (11,"sd4","Mounika",700,"Apparels"),
 (12,"sd4","Mounika",800,"Medicines");

Output:-

The output in image_4 shows that the trigger can re-calculate the total sales and insert it appropriately.

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 before insert

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