MySQL AFTER DELETE Trigger – A Complete Guide

We’ll learn everything about MySQL’s AFTER DELETE trigger in this tutorial. To further understand how the AFTER DELETE trigger works, we’ll start with an introduction and prerequisites before going on to the syntax and an example. If you’re new to MySQL triggers, start with our tutorial on introduction to MySQL triggers.

Introduction and Prerequisites

When the DELETE query is fired on the table, the AFTER DELETE trigger activates. Just after deleting the data from the table, the trigger body of the AFTER DELETE trigger executes.

The trigger body executes after the deletion of the row, therefore you can not modify the old values of the row. However, you can access the old values using the OLD keyword.

Also, the AFTER DELETE trigger doesn’t allow you to use the NEW keyword because there is no new data to be inserted into the table. Overall, the following restrictions are applied to the AFTER DELETE triggers.

• You can access the old values but cannot modify them.
• You cannot use the NEW keyword because there is no new data.
• You cannot create AFTER DELETE trigger on views.

Syntax of MySQL AFTER DELETE Trigger

The CREATE TRIGGER statement is used to create the AFTER DELETE trigger just like any other trigger. Following is the right way to create the AFTER DELETE trigger in MySQL.

CREATE TRIGGER trigger_name
AFTER 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 activation are written in the trigger body. You can also have multiple SQL statements and those can be written inside the BEGIN…END statement

Multiple statements can be written inside the trigger body using the BEGIN…END block. Remember to use any other sign/symbol you like instead of the semicolon, such as an ampersand, brackets, slash, or anything else. The following is the syntax for writing multiple statements within the trigger body.

DELIMITER //
CREATE TRIGGER trigger_name
AFTER 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 AFTER DELETE Trigger

You can use the AFTER DELETE trigger to store the archive-like information such as deleted file names or old details etc. However, the scope of AFTER DELETE is wide and you can implement it depending on your use case.

Here, we will create the AFTER DELETE trigger to store the deleted information about the product into another table. Let’s start with creating the products table first to hold the data of products such as name, price and quantity.

Creating tables

First, let’s create the laptops table and insert data into it. After that, we will create the prod_archives table to store the deleted information from the laptops table.

Create products table-

CREATE TABLE products(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
price FLOAT,
quantity INT
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO products(name,price,quantity)
VALUES("face wash",80,100),("face mask",50,23),
("Hair oil",120,84),("shampoo", 340, 16);Code language: SQL (Structured Query Language) (sql)

Let’s check if the data is inserted correctly in the table.

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

Perfect! Now we will create the prod_archives table in which the exact same data will be inserted along with the date-time as soon as the data from the products table gets deleted.

Create prod_archives table-

CREATE TABLE prod_archives(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
price FLOAT,
quantity INT,
deleted_at DATETIME DEFAULT NOW()
);Code language: SQL (Structured Query Language) (sql)

Here, the deleted_at column is set to the default value of the current date and time.

We are done with the tables. Let’s create a trigger now.

Creating the MySQL AFTER DELETE Trigger

We will create the AFTER DELETE trigger on the table products and insert the new data into the prod_archives table.

DELIMITER //
CREATE TRIGGER after_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
INSERT INTO prod_archives
(name,price,quantity) VALUES
(old.name,old.price,old.quantity);
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here, we have used the OLD keyword to get the old values from the products table and inserted them into the prod_archives table. Note that, the deleted_at column has a default value so no need to insert it explicitly.

Testing the MySQL AFTER DELETE Trigger

Let’s test the trigger by deleting a single row from the products table. After that, we will try to delete multiple rows from the products table to check if the trigger works correctly for multiple rows.

DELETE FROM products
WHERE id = 3;Code language: SQL (Structured Query Language) (sql)

The query was executed successfully. It should delete the row of id 3 from the products table and insert a new row into the prod_archives table. Let’s display both tables-

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

The row from the products table is deleted successfully. Let’s check if the same row is inserted into the prod_archives table.

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

As you can see, the new row is inserted into the table along with the date-time of the deletion. That means our trigger is working perfectly!

Conclusion

Today we learned about the MySQL AFTER DELETE trigger along with an example. We have tried deleting only one row in the above example but you can try deleting as many rows as you want. The trigger will activate for each row of the products table that is going to be deleted. The AFTER DELETE trigger works something like maintaining the summary table of another table.