{"id":8528,"date":"2019-09-07T07:16:16","date_gmt":"2019-09-07T14:16:16","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8528"},"modified":"2025-09-24T17:59:26","modified_gmt":"2025-09-25T00:59:26","slug":"mysql-before-delete-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-before-delete-trigger\/","title":{"rendered":"MySQL BEFORE DELETE Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL BEFORE DELETE<\/code> trigger to add deleted rows into an archive table.<\/p>\n\n\n\n

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

MySQL BEFORE DELETE<\/code> triggers<\/a> are fired automatically before a delete<\/a> event occurs in a table.<\/p>\n\n\n\n

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

CREATE<\/span> TRIGGER<\/span> trigger_name\n    BEFORE<\/span> DELETE<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\ntrigger_body<\/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 which you want to create after the CREATE TRIGGER<\/code> keywords.<\/p>\n\n\n\n
Second, use BEFORE DELETE<\/code> clause to specify that the trigger is invoked right before a delete event.<\/p>\n\n\n\n
Third, specify the name of the table that the trigger is associated with after the ON<\/code> keyword.<\/p>\n\n\n\n
Finally, specify the trigger body which consists of one or more statements that execute when the trigger is fired.<\/p>\n\n\n\n
Notice that if you have multiple statements in the trigger_body<\/code>, you need to use the BEGIN END<\/code> block to wrap these statements and temporarily change the default delimiter as follows:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> trigger_name\n    BEFORE<\/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 a BEFORE DELETE<\/code> trigger, you can access the OLD<\/code> row but cannot update it. Also, there is no NEW<\/code> row in the BEFORE DELETE<\/code> trigger.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL BEFORE DELETE trigger example<\/h2>\n\n\n\n
Let’s see the following BEFORE 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> that stores the salary information of employees<\/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    validFrom DATE<\/span> NOT<\/span> NULL<\/span>,\n    amount DEC<\/span>(12<\/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,validFrom,amount)\nVALUES<\/span>\n    (1002<\/span>,'2000-01-01'<\/span>,50000<\/span>),\n    (1056<\/span>,'2000-01-01'<\/span>,60000<\/span>),\n    (1076<\/span>,'2000-01-01'<\/span>,70000<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> SalaryArchives;    \n\nCREATE<\/span> TABLE<\/span> SalaryArchives (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    employeeNumber INT<\/span> PRIMARY KEY<\/span>,\n    validFrom DATE<\/span> NOT<\/span> NULL<\/span>,\n    amount DEC<\/span>(12<\/span> , 2<\/span> ) NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span>,\n    deletedAt TIMESTAMP<\/span> DEFAULT<\/span> NOW<\/span>()\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating BEFORE DELETE trigger example<\/h3>\n\n\n\n
The following BEFORE DELETE<\/code> trigger inserts a new row into the SalaryArchives<\/code> table before a row from the Salaries<\/code> table is deleted.<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> before_salaries_delete\nBEFORE<\/span> DELETE<\/span>\nON<\/span> salaries FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    INSERT<\/span> INTO<\/span> SalaryArchives(employeeNumber,validFrom,amount)\n    VALUES<\/span>(OLD.employeeNumber,OLD.validFrom,OLD.amount);\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 this trigger:<\/p>\n\n\n\n
First, the name of the trigger is before_salaries_delete<\/code> specified in the CREATE TRIGGER<\/code> clause:<\/p>\n\n\n
CREATE<\/span> TRIGGER<\/span> before_salaries_delete\n<\/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
BEFORE DELETE<\/span>\n<\/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\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, inside the trigger body insert a deleted row into the SalaryArchives<\/code> table.<\/p>\n\n\n\n
Testing the MySQL BEFORE DELETE trigger<\/h3>\n\n\n\n
First, delete a row from the Salaries<\/code> 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 data from the SalaryArchives<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalaryArchives;    <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The trigger was invoked and inserted a new row into the SalaryArchives<\/code> table.<\/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 data from the SalaryArchives<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalaryArchives;<\/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 was triggered twice because the DELETE<\/code> statement deleted two rows from the Salaries<\/code> table.<\/p>\n\n\n\n
In this tutorial, you have learned how to create a MySQL BEFORE DELETE<\/code> trigger to add deleted rows into an archive 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
Third, create a table<\/a> that stores the deleted salary:<\/p>\n\n\n
Second, insert some rows<\/a> into the Salaries<\/code> table:<\/p>\n\n\n