DELETE triggers in MySQL - Mini Project: Build & Apply

Using DELETE Triggers in MySQL

📖 Scenario: You manage a small online store database. You want to keep track of every product that gets deleted from the products table by saving its details in a separate deleted_products table.

🎯 Goal: Create a DELETE trigger in MySQL that automatically copies the deleted product's information into the deleted_products table whenever a product is removed from the products table.

📋 What You'll Learn

Create a products table with columns product_id (INT), product_name (VARCHAR(50)), and price (DECIMAL(10,2))

Create a deleted_products table with the same columns as products plus a deleted_at column (DATETIME)

Create a DELETE trigger named after_product_delete on the products table

The trigger should insert the deleted row's data into deleted_products with the current timestamp in deleted_at

💡 Why This Matters

🌍 Real World

DELETE triggers help keep audit logs or backups automatically when data is removed, which is important for data safety and tracking changes in real-world databases.

💼 Career

Understanding triggers is useful for database administrators and backend developers who maintain data integrity and automate database tasks.

1

Create the products table

Write the SQL statement to create a table called products with columns: product_id as INT primary key, product_name as VARCHAR(50), and price as DECIMAL(10,2).

MySQL

-- Your SQL code to create the products table here

Need a hint?

Use CREATE TABLE with the specified columns and types. Remember to set product_id as the primary key.

2

Create the deleted_products table

Write the SQL statement to create a table called deleted_products with columns: product_id INT, product_name VARCHAR(50), price DECIMAL(10,2), and deleted_at DATETIME.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  price DECIMAL(10,2)
);
-- Your SQL code to create the deleted_products table here

Need a hint?

Make sure to add the deleted_at column with type DATETIME to store when the product was deleted.

3

Create the DELETE trigger

Write the SQL statement to create a trigger named after_product_delete that runs AFTER DELETE on the products table. The trigger should insert the deleted row's product_id, product_name, and price into deleted_products and set deleted_at to the current timestamp using NOW().

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE deleted_products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2),
  deleted_at DATETIME
);
-- Your SQL code to create the DELETE trigger here

Need a hint?

Use OLD.column_name to access the deleted row's values inside the trigger. Remember to change the delimiter before and after the trigger creation.

4

Test the DELETE trigger

Write the SQL statements to insert a product with product_id 1, product_name 'Notebook', and price 9.99 into products. Then delete this product from products to activate the trigger.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE deleted_products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2),
  deleted_at DATETIME
);

DELIMITER $$
CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
  INSERT INTO deleted_products (product_id, product_name, price, deleted_at)
  VALUES (OLD.product_id, OLD.product_name, OLD.price, NOW());
END$$
DELIMITER ;

-- Your SQL code to insert and delete a product here

Need a hint?

Insert the product first, then delete it to trigger the DELETE trigger.