In this article, we’ll learn everything about MySQL’s BEFORE DELETE trigger. We’ll start with an introduction and prerequisites before moving on to the syntax and an example to further know how the BEFORE DELETE trigger works. If you’re new to MySQL triggers, you may start by reading our introduction to MySQL triggers tutorial. Individual tutorials for all types of MySQL triggers have been created and can be found using the search bar on our website.
Introduction and Prerequisites
When the DELETE query is fired on the table, the BEFORE DELETE trigger activates. Just before deleting the data from the table, the trigger body of the BEFORE DELETE trigger executes.
Because the data is going to be deleted using the DELETE query, you can not modify the old data of the row. However, you can access the old data using the OLD keyword.
The DELETE triggers do not allow using the NEW keyword because there is no new value to be inserted in the row. Therefore, avoid using the NEW keyword in the BEFORE DELETE trigger in order to successfully execute the query. Overall, the following are the restrictions on BEFORE DELETE trigger.
- You can access the old values using the OLD keyword but cannot modify them.
- You cannot use the NEW keyword in BEFORE DELETE triggers.
- You cannot create BEFORE DELETE trigger on views.
Syntax of MySQL BEFORE DELETE Trigger
You can create the BEFORE DELETE trigger using CREATE TRIGGER statement like any other trigger. Following is the right way to create the BEFORE DELETE trigger-
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
[trigger_order]
trigger_bodyCode language: SQL (Structured Query Language) (sql)Where,
- trigger_name – This can be any name but no two triggers of the same name can reside in the same database.
- trigger_order – This is the order in which the trigger activates. If two or more triggers are associated with the same table then you can use FOLLOWS and PRECEDES keywords to specify the order in which triggers should activate. However, trigger_order is an optional parameter.
- trigger_body – The set of operations after trigger activates are written in the trigger body. You can also have multiple SQL statements and those can be written inside the BEGIN…END statement.
The BEGIN…END block can be used to write multiple statements inside the trigger body. Remember to replace the semicolon with any other sign/symbol you like, such as an ampersand, brackets, slash, or anything else. The syntax for writing multiple statements within the trigger body is as follows.
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
[trigger_order]
BEGIN
trigger_body
END
DELIMITER ;Code language: SQL (Structured Query Language) (sql)Where,
- BEGIN…END – It is a block in which you can write multiple SQL statements to perform specific operations.
- DELIMITER // – changes the delimiter from (;) to (//).
- DELIMITER ; – changes the delimiter from (//) to (;).
Example of MySQL BEFORE DELETE Trigger
You can use the BEFORE DELETE trigger to store the records to be deleted into the other table for an archive purpose. You can think of it as a recycle bin of the computer where all the deleted files from your computer are stored when deleted by the user. We are going to accomplish the same task using the BEFORE DELETE trigger here.
Creating tables
First, let’s create a table to store the details of the laptops and insert data into it.
Create laptops table-
CREATE TABLE laptops(
id INT PRIMARY KEY AUTO_INCREMENT,
model_no INT,
model_name VARCHAR(100),
price FLOAT,
insurance FLOAT,
quantity INT
);Code language: SQL (Structured Query Language) (sql)Insert data into the table-
INSERT INTO laptops(model_no,model_name,price,insurance,quantity)
VALUES(12314,"Lenovo Ideapad",48700,180,5),
(57823,"Dell Vostro 3000",40990,130,9),
(17827,"Asus Vivobook",35000,130,8),
(12879,"HP Ryzen 3",39990,120,9),
(23098,"Asus Pentium Q",28990,180,4),
(18778,"Dell Inspiron 3",45990,130,5);Code language: SQL (Structured Query Language) (sql)Let’s check if the data is inserted correctly in the table.
SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)
Now we will create a laptops_archive table in which all the information of the product will be stored when we delete that product from the laptops table.
Create the laptops_archive table-
CREATE TABLE laptops_archive(
id INT PRIMARY KEY AUTO_INCREMENT,
model_no INT,
model_name VARCHAR(100),
price FLOAT,
insurance FLOAT,
deleted_at DATETIME DEFAULT NOW()
);Code language: SQL (Structured Query Language) (sql)
Perfect! let’s create a trigger now.
Creating the MySQL BEFORE DELETE Trigger
Using the trigger, we will store the data of the laptops table that we are going to delete into the laptops_archive table with the date-time of the deletion.
Here, we will create a BEFORE DELETE trigger on the laptops table and insert the data into the laptops_achive table.
DELIMITER //
CREATE TRIGGER before_delete
BEFORE DELETE ON laptops
FOR EACH ROW
BEGIN
INSERT INTO laptops_archive(
model_no,model_name,price,insurance)
VALUES(old.model_no,old.model_name,
old.price, old.insurance);
END
//Code language: SQL (Structured Query Language) (sql)
Here, we have inserted all the old values from the laptops table using the OLD keyword into the laptops_archive table. Note that, the deleted_at column has a default value so no need to insert it explicitly.
Testing the MySQL BEFORE DELETE Trigger
Let’s delete one record from the laptops table to test the trigger.
DELETE FROM laptops
WHERE id = 5;Code language: SQL (Structured Query Language) (sql)The query was executed successfully. It should delete the row of id 5 from the laptops table and insert a new row into the laptops_archive table. Let’s display both tables.
display laptops table-
SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)
As you can see, the data is deleted from the laptops table. Let’s see the laptops_archive table now.
SELECT * FROM laptops_archive;Code language: SQL (Structured Query Language) (sql)
The new row is inserted into the table. That means the trigger is working fine and perfect!
Let’s try to delete two rows from the laptops table and see if the two rows get inserted into the laptops_archive table.
DELETE FROM laptops
WHERE id IN (2,3);Code language: SQL (Structured Query Language) (sql)Let’s check the laptops_archive table now to find if the two rows are inserted into it.
SELECT * FROM laptops_archive;Code language: SQL (Structured Query Language) (sql)
As you can see, two rows are inserted into the laptops_archive table because the trigger was activated twice on the laptops table.
Overall, our trigger is working very well!
Conclusion
You can create different types of BEFORE DELETE triggers using your own ideas and use cases. You can implement it in your project to create a backup-like or archive-like system where you can store the data of deleted records such as user information so that users can restore their data without any problem. I hope you liked this tutorial and understood the topic. See you in the following tutorial!