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

Using BEFORE UPDATE Triggers in MySQL

📖 Scenario: You manage a small online store database. You want to keep track of when product prices change by automatically recording the old price before any update.

🎯 Goal: Create a MySQL BEFORE UPDATE trigger that saves the old price of a product into a separate column before the price is updated.

📋 What You'll Learn

Create a table called products with columns id, name, price, and old_price

Add a BEFORE UPDATE trigger named before_price_update on the products table

The trigger should copy the current price value into old_price before the update happens

Test the trigger by updating a product's price

💡 Why This Matters

🌍 Real World

BEFORE UPDATE triggers help keep track of changes automatically in databases, useful for auditing and history tracking.

💼 Career

Database developers and administrators use triggers to enforce business rules and maintain data integrity without manual intervention.

1

Create the products table

Create a table called products with these columns: id as INT primary key, name as VARCHAR(50), price as DECIMAL(10,2), and old_price as DECIMAL(10,2) that can be NULL.

MySQL

-- Your code here to create the products table

Need a hint?

Use CREATE TABLE with the exact column names and types.

2

Insert sample data into products

Insert a product into products with id 1, name 'Coffee Mug', and price 12.99. Leave old_price as NULL.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  old_price DECIMAL(10,2) NULL
);
-- Your code here to insert the sample product

Need a hint?

Use INSERT INTO products (id, name, price, old_price) with the exact values.

3

Create the BEFORE UPDATE trigger

Create a BEFORE UPDATE trigger named before_price_update on the products table that sets NEW.old_price to the current OLD.price before the update.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  old_price DECIMAL(10,2) NULL
);

INSERT INTO products (id, name, price, old_price) VALUES (1, 'Coffee Mug', 12.99, NULL);
-- Your code here to create the BEFORE UPDATE trigger

Need a hint?

Use DELIMITER $$ to define the trigger, then reset delimiter after.

4

Update the product price to test the trigger

Write an UPDATE statement to change the price of the product with id 1 to 15.99. This will activate the trigger and set old_price automatically.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  old_price DECIMAL(10,2) NULL
);

INSERT INTO products (id, name, price, old_price) VALUES (1, 'Coffee Mug', 12.99, NULL);

DELIMITER $$
CREATE TRIGGER before_price_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SET NEW.old_price = OLD.price;
END$$
DELIMITER ;
-- Your code here to update the product price

Need a hint?

Use UPDATE products SET price = 15.99 WHERE id = 1; exactly.