MySQL AFTER DELETE Trigger
Summary: in this tutorial, you will learn how to create a MySQL AFTER DELETE
trigger to maintain a summary table of another table.
Introduction to MySQL AFTER DELETE
triggers
MySQL AFTER DELETE
triggers are automatically invoked after a delete event occurs on the table.
Here is the basic syntax of creating a MySQL AFTER DELETE
trigger:
CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
trigger_body;
In this syntax:
First, specify the name of the trigger that you want to create in the CREATE TRIGGER
clause.
Second, use AFTER DELETE
clause to specify the time to invoke the trigger.
Third, specify the name of the table, which the trigger is associated with, after the ON
keyword.
Finally, specify the trigger body which contains one or more statements that execute when the trigger is invoked.
If you have multiple statements in the trigger_body
, you need to use the BEGIN END
block to wrap them and flip the default delimiter between $$
and ;
as shown in the following:
DELIMITER $$
CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
In an AFTER DELETE
trigger, you can access the OLD
row but cannot change it.
Note that there is no NEW
row in the AFTER DELETE
trigger.
MySQL AFTER DELETE
trigger example
Consider the following AFTER DELETE
trigger example.
Setting up a sample table
First, create a new table called Salaries
:
DROP TABLE IF EXISTS Salaries;
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
salary DECIMAL(10,2) NOT NULL DEFAULT 0
);
Second, insert some rows into the Salaries
table:
INSERT INTO Salaries(employeeNumber,salary)
VALUES
(1002,5000),
(1056,7000),
(1076,8000);
Third, create another table called SalaryBudgets
that stores the total of salaries from the Salaries
table:
DROP TABLE IF EXISTS SalaryBudgets;
CREATE TABLE SalaryBudgets(
total DECIMAL(15,2) NOT NULL
);
Fourth, use the SUM()
function to get the total salary from the Salaries
table and insert it into the SalaryBudgets
table:
INSERT INTO SalaryBudgets(total)
SELECT SUM(salary)
FROM Salaries;
Finally, query data from the SalaryBudgets
table:
SELECT * FROM SalaryBudgets;
Creating AFTER DELETE
trigger example
The following AFTER DELETE
trigger updates the total salary in the SalaryBudgets
table after a row is deleted from the Salaries
table:
CREATE TRIGGER after_salaries_delete
AFTER DELETE
ON Salaries FOR EACH ROW
UPDATE SalaryBudgets
SET total = total - old.salary;
In this trigger:
First, the name of the trigger is after_salaries_delete
specified in the CREATE TRIGGER
clause:
CREATE TRIGGER after_salaries_delete
Second, the triggering event is:
AFTER DELETE
Third, the table that the trigger is associated with is Salaries
table:
ON Salaries FOR EACH ROW
Finally, inside the trigger body, we subtract the deleted salary from the total salary.
Testing the MySQL AFTER DELETE
trigger
First, delete a row from the Salaries table:
DELETE FROM Salaries
WHERE employeeNumber = 1002;
Second, query total salary from the SalaryBudgets table:
SELECT * FROM SalaryBudgets;
As you can see from the output, the total is reduced by the deleted salary.
Third, delete all rows from the salaries table:
DELETE FROM Salaries;
Finally, query the total from the SalaryBudgets table:
SELECT * FROM SalaryBudgets;
The trigger updated the total to zero.
In this tutorial, you have learned how to create a MySQL AFTER DELETE
trigger to maintain a summary table of another table.
0 Comments
CAN FEEDBACK
Emoji