Create trigger on delete

In this article, we will discuss triggers on delete with practical examples. A trigger is a database object linked with a table that automatically activates when a particular event occurs on the table.

The row operations or conditions that turn on a trigger are known as trigger events and can activate triggers before or after their occurrence. The trigger event we will be discussing in this article is DELETE. Therefore, we will see how to create both AFTER DELETE and BEFORE DELETE triggers.

READ MORE ABOUT TRIGGERS: What are triggers in MySQL

Table of Contents:-

• MySQL Trigger on Delete Syntax
• MySQL Trigger AFTER DELETE
• MySQL Trigger BEFORE DELETE

Before moving ahead, let us prepare the sample data. We will be creating two tables, sale_details and total_sales, followed by inserting some rows into them. The total_sales table stores the total no of products sold per sale department.

#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)
);
# insert rows to 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");
#create the table total_sales
CREATE TABLE total_sales (
    id INT AUTO_INCREMENT,
    sales_department  VARCHAR(255) ,
	total_sale INT,
    PRIMARY KEY(id)
);
# insert rows to total_sales
INSERT INTO total_sales (id,sales_department,total_sale) 
 VALUES(1,"Kitchen Essentials",5100),
 (2,"Apparels",5650),
 (3,"Medicines",1090);

SELECT * FROM sale_details;

Output:-

SELECT * FROM total_sales;

Output:-

MySQL Trigger on Delete Syntax

Syntax:-

CREATE TRIGGER name_of_your_trigger
{BEFORE | AFTER} DELETE
ON name_of_your_table FOR EACH ROW
trigger_body;

• name_of_your_trigger is the name given to your trigger.
• BEFORE | AFTER are the action time modifiers that is trigger can be activated either before or after the event.
• name_of_your_table is the table linked with the trigger. Deletion 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.
• The NEW keyword is not available for DELETE (BEFORE and AFTER) triggers.

MySQL Trigger Example: AFTER DELETE

Example: Update the total_sale column concerning the sales_department from the total_sales table if any row gets deleted from the sale_details table.

Execute the below trigger.

DELIMITER //
CREATE TRIGGER  after_sale_details_delete
    AFTER DELETE 
    ON sale_details FOR EACH ROW 
 BEGIN
  UPDATE total_sales SET total_sale = total_sale - OLD.no_products_sold WHERE sales_department = OLD.sales_department; 
 END; // 

Action Output:-

Testing the trigger:-

To test if the trigger is working correctly, let us perform a delete operation on the sale_details table. We will be deleting entries from the sale_details table where sale_person_id = ‘sd1’ and then view the total_sales table.

DELETE FROM sale_details where sale_person_id = 'sd1';

Action Output:-

SELECT * FROM total_sales;

Output:-

Comparing image_5 with image_2 will ensure that total_sale gets recalculated for all three departments.

MySQL Trigger Example: BEFORE DELETE

Example: Update the total_sale concerning the sales_department from the total_sales table before any row gets deleted from the sale_details table.

Execute the below trigger.

DELIMITER //
CREATE TRIGGER  before_sale_details_delete
    BEFORE DELETE 
    ON sale_details FOR EACH ROW 
 BEGIN
  UPDATE total_sales SET total_sale = total_sale - OLD.no_products_sold WHERE sales_department = OLD.sales_department; 
 END; // 

Action Output:-

Testing the trigger:-

To test if the trigger is working correctly, let us operate delete on the sale_details table. We will be deleting entries from the sale_details table where sale_person_id = ‘sd2’ and then view the total_sales table.

NOTE: We are dropping the trigger after_sale_details_delete before proceeding to avoid confusion.

DROP TRIGGER  after_sale_details_delete;
DELETE FROM sale_details where sale_person_id = 'sd2';

Action Output:-

SELECT * FROM total_sales;

Output:-

The output in image_8 shows that total_sale gets recalculated for all three departments.

READ MORE:

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

We hope this article helped you to understand how to create triggers on delete. Good Luck!!!