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:-
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:-
Frequently Asked:
- MySQL ADD FOREIGN KEY
- How to get MySQL version
- MySQL Select where Count is greater than one [Solved]
- Select all columns except one of a MySQL table

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!!!