{"id":8553,"date":"2019-09-07T09:21:21","date_gmt":"2019-09-07T16:21:21","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8553"},"modified":"2025-09-24T18:00:04","modified_gmt":"2025-09-25T01:00:04","slug":"mysql-after-delete-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-after-delete-trigger\/","title":{"rendered":"MySQL AFTER DELETE Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL AFTER DELETE<\/code> trigger to maintain a summary table of another table.<\/p>\n\n\n\n

Introduction to MySQL AFTER DELETE triggers<\/h2>\n\n\n\n

MySQL AFTER DELETE<\/code>  triggers<\/a> are automatically invoked after a delete<\/a> event occurs on the table.<\/p>\n\n\n\n

Here is the basic syntax of creating a MySQL AFTER DELETE<\/code> trigger:<\/p>\n\n\n

CREATE<\/span> TRIGGER<\/span> trigger_name\n    AFTER<\/span> DELETE<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\ntrigger_body;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name of the trigger that you want to create in the CREATE TRIGGER<\/code> clause.<\/p>\n\n\n\n
Second, use AFTER DELETE<\/code> clause to specify the time to invoke the trigger.<\/p>\n\n\n\n
Third, specify the name of the table, which the trigger is associated with, after the ON<\/code> keyword.<\/p>\n\n\n\n
Finally, specify the trigger body which contains one or more statements that execute when the trigger is invoked.<\/p>\n\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> trigger_name\n    AFTER<\/span> DELETE<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    -- statements<\/span>\nEND<\/span>$$    \n\nDELIMITER ;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In an AFTER DELETE<\/code> trigger, you can access the OLD<\/code> row but cannot change it.<\/p>\n\n\n\n
Note that there is no NEW<\/code> row in the AFTER DELETE<\/code> trigger.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL AFTER DELETE trigger example<\/h2>\n\n\n\n
Consider the following AFTER DELETE<\/code> trigger example.<\/p>\n\n\n\n
Setting up a sample table<\/h3>\n\n\n\n
First, create a new table<\/a> called Salaries<\/code>:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> Salaries;\n\nCREATE<\/span> TABLE<\/span> Salaries (\n    employeeNumber INT<\/span> PRIMARY KEY<\/span>,\n    salary DECIMAL<\/span>(10<\/span>,2<\/span>) NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> Salaries(employeeNumber,salary)\nVALUES<\/span>\n    (1002<\/span>,5000<\/span>),\n    (1056<\/span>,7000<\/span>),\n    (1076<\/span>,8000<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create another table called SalaryBudgets<\/code> that stores the total of salaries from the Salaries<\/code> table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> SalaryBudgets;\n\nCREATE<\/span> TABLE<\/span> SalaryBudgets(\n    total DECIMAL<\/span>(15<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> SalaryBudgets(total)\nSELECT<\/span> SUM<\/span>(salary) \nFROM<\/span> Salaries;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> SalaryBudgets;        <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Creating AFTER DELETE trigger example<\/h3>\n\n\n\n
The following AFTER DELETE<\/code> trigger updates the total salary in the SalaryBudgets<\/code> table after a row is deleted from the Salaries<\/code> table:<\/p>\n\n\n
CREATE<\/span> TRIGGER<\/span> after_salaries_delete\nAFTER<\/span> DELETE<\/span>\nON<\/span> Salaries FOR<\/span> EACH<\/span> ROW<\/span>\nUPDATE<\/span> SalaryBudgets \nSET<\/span> total = total - old.salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this trigger:<\/p>\n\n\n\n
First, the name of the trigger is after_salaries_delete<\/code> specified in the CREATE TRIGGER<\/code> clause:<\/p>\n\n\n
CREATE<\/span> TRIGGER<\/span> after_salaries_delete<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, the triggering event is:<\/p>\n\n\n
AFTER DELETE<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, the table that the trigger is associated with is Salaries<\/code> table:<\/p>\n\n\n
ON Salaries FOR EACH ROW<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, inside the trigger body, we subtract the deleted salary from the total salary.<\/p>\n\n\n\n
Testing the MySQL AFTER DELETE trigger<\/h3>\n\n\n\n
First, delete a row from the Salaries table:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> Salaries\nWHERE<\/span> employeeNumber = 1002<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, query the total salary from the SalaryBudgets table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalaryBudgets;    <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
As you can see from the output, the total is reduced by the deleted salary.<\/p>\n\n\n\n
Third, delete all rows from the salaries table:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> Salaries;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query the total from the SalaryBudgets table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalaryBudgets;    <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The trigger updated the total to zero.<\/p>\n\n\n\n
In this tutorial, you have learned how to create a MySQL AFTER DELETE<\/code> trigger to maintain a summary table of another table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Finally, query data<\/a> from the SalaryBudgets<\/code> table:<\/p>\n\n\n
Fourth, use the SUM()<\/a><\/code> function to get the total salary from the Salaries<\/code> table and insert it into the SalaryBudgets<\/code> table:<\/p>\n\n\n
Second, insert some rows<\/a> into the Salaries<\/code> table:<\/p>\n\n\n
If you have multiple statements in the trigger_body<\/code>, you need to use the BEGIN END<\/code> block to wrap them and flip the default delimiter<\/a> between $$<\/code> and ;<\/code> as shown in the following:<\/p>\n\n\n