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;
Code language: SQL (Structured Query Language) (sql)

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 ;
Code language: SQL (Structured Query Language) (sql)

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
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the Salaries table:

INSERT INTO Salaries(employeeNumber,salary)
VALUES
    (1002,5000),
    (1056,7000),
    (1076,8000);
Code language: SQL (Structured Query Language) (sql)

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
);
Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Finally, query data from the SalaryBudgets table:

SELECT * FROM SalaryBudgets;        Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

In this trigger:

First, the name of the trigger is after_salaries_delete specified in the CREATE TRIGGER clause:

CREATE TRIGGER after_salaries_deleteCode language: SQL (Structured Query Language) (sql)

Second, the triggering event is:

AFTER DELETECode language: SQL (Structured Query Language) (sql)

Third, the table that the trigger is associated with is Salaries table:

ON Salaries FOR EACH ROWCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Second, query the total salary from the SalaryBudgets table:

SELECT * FROM SalaryBudgets;    Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Finally, query the total from the SalaryBudgets table:

SELECT * FROM SalaryBudgets;    Code language: SQL (Structured Query Language) (sql)

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.