AFTER UPDATE triggers in MySQL - Mini Project: Build & Apply

Using AFTER UPDATE Triggers in MySQL

📖 Scenario: You manage a small online store database. You want to keep track of every time a product's price changes. This helps you analyze price updates over time.

🎯 Goal: Create a MySQL AFTER UPDATE trigger that records old and new prices into a separate price_changes table whenever a product's price is updated.

📋 What You'll Learn

Create a products table with columns product_id, product_name, and price.

Create a price_changes table with columns change_id, product_id, old_price, new_price, and change_date.

Create an AFTER UPDATE trigger on products that inserts a record into price_changes when the price changes.

Ensure the trigger only records changes if the price is actually updated.

💡 Why This Matters

🌍 Real World

Tracking price changes helps businesses analyze pricing strategies and maintain audit trails for product updates.

💼 Career

Database triggers are widely used in real-world applications to automate tasks and enforce business rules without extra application code.

1

Create the products table

Create a table called products with columns: product_id as an integer primary key, product_name as a VARCHAR(100), and price as a DECIMAL(10,2).

MySQL

-- Create the products table
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Create the price_changes table

Create a table called price_changes with columns: change_id as an auto-increment integer primary key, product_id as an integer, old_price and new_price as DECIMAL(10,2), and change_date as a DATETIME.

MySQL

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

-- Create the price_changes table
-- Your code here

Need a hint?

Remember to use AUTO_INCREMENT for change_id and proper data types for prices and date.

3

Create the AFTER UPDATE trigger

Create an AFTER UPDATE trigger named after_price_update on the products table. The trigger should insert a row into price_changes with product_id, old_price (from OLD.price), new_price (from NEW.price), and the current timestamp as change_date. Only insert if OLD.price is different from NEW.price.

MySQL

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

CREATE TABLE price_changes (
  change_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  old_price DECIMAL(10,2),
  new_price DECIMAL(10,2),
  change_date DATETIME
);

-- Create AFTER UPDATE trigger
-- Your code here

Need a hint?

Use DELIMITER $$ to define the trigger body and compare OLD.price and NEW.price.

4

Test and finalize the trigger setup

Add a comment at the end of your code to indicate the trigger is ready: -- Trigger after_price_update is set up

MySQL

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

CREATE TABLE price_changes (
  change_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  old_price DECIMAL(10,2),
  new_price DECIMAL(10,2),
  change_date DATETIME
);

DELIMITER $$
CREATE TRIGGER after_price_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF OLD.price  NEW.price THEN
    INSERT INTO price_changes(product_id, old_price, new_price, change_date)
    VALUES (NEW.product_id, OLD.price, NEW.price, NOW());
  END IF;
END$$
DELIMITER ;

-- Add a comment to confirm trigger setup
-- Your code here

Need a hint?

Simply add the comment exactly as shown to mark completion.