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

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

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

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

Hint

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

Practice

1. What is the main purpose of a DELETE trigger in MySQL?

easy

A. To automatically run code when a row is deleted

B. To insert new rows into a table

C. To update rows before selecting them

D. To create new tables dynamically

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when certain actions happen on a table, like DELETE.

2. Step 2: Identify the purpose of DELETE triggers

   DELETE triggers specifically run when rows are deleted to perform extra tasks.

3. Final Answer:

   To automatically run code when a row is deleted -> Option A

4. Quick Check:

   DELETE triggers = automatic code on row deletion [OK]

Hint: Triggers run automatically on data changes like DELETE [OK]

Common Mistakes:

• Confusing DELETE triggers with INSERT or UPDATE triggers
• Thinking triggers create or modify tables
• Believing triggers run only manually

2. Which of the following is the correct syntax to create a BEFORE DELETE trigger in MySQL?

easy

A. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg AFTER DELETE ON table_name FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE DELETE FROM table_name BEGIN ... END;

Solution

1. Step 1: Recall the correct trigger syntax

   MySQL triggers use CREATE TRIGGER name BEFORE|AFTER event ON table FOR EACH ROW BEGIN ... END;

2. Step 2: Match syntax for BEFORE DELETE

   CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; matches this exactly with BEFORE DELETE and correct structure.

3. Final Answer:

   CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; -> Option A

4. Quick Check:

   Correct syntax = CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: Syntax: CREATE TRIGGER name BEFORE|AFTER DELETE ON table [OK]

Common Mistakes:

• Using AFTER instead of BEFORE when asked
• Wrong event like UPDATE instead of DELETE
• Missing FOR EACH ROW clause

3. Given this trigger and table:

CREATE TABLE users (id INT, name VARCHAR(20));
CREATE TRIGGER log_delete AFTER DELETE ON users FOR EACH ROW BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

What happens when DELETE FROM users WHERE id=1; is executed?

medium

A. The row with id=1 is deleted but nothing is logged

B. The row with id=1 is deleted and its data is inserted into deleted_log

C. The DELETE statement fails with a syntax error

D. The row with id=1 is not deleted because of the trigger

Solution

1. Step 1: Understand AFTER DELETE trigger behavior

   The trigger runs after a row is deleted and inserts OLD row data into deleted_log.

2. Step 2: Analyze the DELETE command effect

   Deleting id=1 removes that row and the trigger logs its data into deleted_log.

3. Final Answer:

   The row with id=1 is deleted and its data is inserted into deleted_log -> Option B

4. Quick Check:

   AFTER DELETE trigger logs deleted row [OK]

Hint: AFTER DELETE triggers run after row removal to log data [OK]

Common Mistakes:

• Thinking trigger prevents deletion
• Assuming no logging happens
• Confusing OLD with NEW in DELETE triggers

4. Identify the error in this trigger definition:

CREATE TRIGGER trg BEFORE DELETE ON users
BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

medium

A. Using NEW in a DELETE trigger is invalid

B. BEFORE DELETE triggers cannot modify data

C. Trigger body must be a single statement without BEGIN...END

D. Missing FOR EACH ROW clause

Solution

1. Step 1: Check trigger syntax requirements

   MySQL requires FOR EACH ROW clause in row-level triggers.

2. Step 2: Identify missing FOR EACH ROW

   The trigger lacks FOR EACH ROW, causing syntax error.

3. Final Answer:

   Missing FOR EACH ROW clause -> Option D

4. Quick Check:

   FOR EACH ROW is mandatory in triggers [OK]

Hint: Always include FOR EACH ROW in row-level triggers [OK]

Common Mistakes:

• Using NEW in DELETE triggers (allowed only OLD)
• Omitting FOR EACH ROW clause
• Incorrect BEGIN...END usage

5. You want to prevent deletion of rows in the orders table if the order status is 'shipped'. Which DELETE trigger logic is correct?

hard

A. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN DELETE FROM orders WHERE id = OLD.id; END IF; END;

B. CREATE TRIGGER trg AFTER DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN ROLLBACK; END IF; END;

C. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END;

D. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN UPDATE orders SET status = 'cancelled' WHERE id = OLD.id; END IF; END;

Solution

1. Step 1: Understand how to prevent deletion

   Use BEFORE DELETE trigger and SIGNAL to raise an error and stop deletion.

2. Step 2: Analyze each option's logic

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; correctly uses BEFORE DELETE, checks OLD.status, and signals error to prevent deletion.

3. Final Answer:

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; -> Option C

4. Quick Check:

   Use SIGNAL in BEFORE DELETE to block deletion [OK]

Hint: Use SIGNAL in BEFORE DELETE to stop unwanted deletions [OK]

Common Mistakes:

• Using AFTER DELETE to prevent deletion (too late)
• Using NEW in DELETE triggers (only OLD is valid)
• Trying to delete inside a DELETE trigger causing recursion