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

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

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

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

Hint

Simply add the comment exactly as shown to mark completion.

Practice

1. What is the main purpose of an AFTER UPDATE trigger in MySQL?

easy

A. To execute actions automatically after a row is updated in a table

B. To prevent updates from happening on a table

C. To execute actions before a row is inserted

D. To delete rows after an update

Solution

1. Step 1: Understand trigger timing

   An AFTER UPDATE trigger runs after a row in a table is updated.

2. Step 2: Identify trigger purpose

   Its purpose is to perform extra actions automatically after the update, like logging or updating related data.

3. Final Answer:

   To execute actions automatically after a row is updated in a table -> Option A

4. Quick Check:

   AFTER UPDATE triggers run after update [OK]

Hint: AFTER UPDATE triggers run after row changes [OK]

Common Mistakes:

• Confusing AFTER UPDATE with BEFORE UPDATE
• Thinking triggers prevent updates
• Mixing trigger types like INSERT or DELETE

2. Which of the following is the correct syntax to create an AFTER UPDATE trigger on a table named employees?

easy

A. CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN END;

B. CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN END;

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

D. CREATE TRIGGER trg AFTER UPDATE employees FOR EACH ROW BEGIN END;

Solution

1. Step 1: Check trigger timing and event

   The trigger must be AFTER UPDATE on the employees table.

2. Step 2: Verify syntax correctness

   CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN END; uses correct syntax: CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN END;

3. Final Answer:

   CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN END; -> Option B

4. Quick Check:

   Correct syntax includes 'AFTER UPDATE ON table' [OK]

Hint: Syntax: CREATE TRIGGER name AFTER UPDATE ON table [OK]

Common Mistakes:

• Omitting ON keyword before table name
• Using BEFORE instead of AFTER
• Using wrong event like INSERT

3. Given this trigger on table products:

CREATE TRIGGER trg_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (product_id, old_price, new_price)
  VALUES (OLD.id, OLD.price, NEW.price);
END;

What happens when a product's price is updated?

medium

A. The audit_log table is deleted

B. The product's price is reverted to the old price

C. The update is blocked and no changes happen

D. A new row is added to audit_log with old and new prices

Solution

1. Step 1: Understand trigger action

   The trigger inserts a row into audit_log after a product update.

2. Step 2: Analyze inserted values

   It records OLD.id, OLD.price, and NEW.price to log price changes.

3. Final Answer:

   A new row is added to audit_log with old and new prices -> Option D

4. Quick Check:

   AFTER UPDATE triggers can log changes [OK]

Hint: AFTER UPDATE can log old and new values [OK]

Common Mistakes:

• Thinking trigger changes original update
• Confusing OLD and NEW values
• Assuming trigger blocks update

4. You wrote this trigger but get an error:

CREATE TRIGGER trg AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  SET NEW.status = 'processed';
END;

What is the problem?

medium

A. The syntax for SET is incorrect inside triggers

B. The trigger must be BEFORE UPDATE to modify NEW values

C. You cannot modify NEW values in an AFTER UPDATE trigger

D. Triggers cannot use BEGIN...END blocks

Solution

1. Step 1: Understand trigger timing restrictions

   In AFTER UPDATE triggers, NEW values are read-only and cannot be changed.

2. Step 2: Identify correct timing to modify NEW

   To modify NEW values, use BEFORE UPDATE triggers instead.

3. Final Answer:

   You cannot modify NEW values in an AFTER UPDATE trigger -> Option C

4. Quick Check:

   AFTER UPDATE triggers cannot change NEW row [OK]

Hint: Modify NEW only in BEFORE UPDATE triggers [OK]

Common Mistakes:

• Trying to change NEW in AFTER UPDATE triggers
• Confusing BEFORE and AFTER triggers
• Incorrect SET syntax blamed

5. You want to keep a count of how many times each user's email was updated in a table users. Which trigger code correctly updates a email_update_count column after each email change?

hard

A. CREATE TRIGGER trg_email_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email != NEW.email THEN
SET NEW.email_update_count = OLD.email_update_count + 1;
END IF;
END;

B. CREATE TRIGGER trg_email_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email != NEW.email THEN
UPDATE users SET email_update_count = email_update_count + 1 WHERE id = NEW.id;
END IF;
END;

C. CREATE TRIGGER trg_email_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.email_update_count = OLD.email_update_count + 1;
END;

D. CREATE TRIGGER trg_email_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email = NEW.email THEN
UPDATE users SET email_update_count = email_update_count + 1 WHERE id = NEW.id;
END IF;
END;

Solution

1. Step 1: Identify when to modify the count

   The count should increase only if the email changes, so check if OLD.email != NEW.email.

2. Step 2: Choose correct trigger timing to modify NEW

   To change email_update_count in the same row, use a BEFORE UPDATE trigger and set NEW.email_update_count.

3. Step 3: Analyze options

   CREATE TRIGGER trg_email_update
   BEFORE UPDATE ON users
   FOR EACH ROW
   BEGIN
     IF OLD.email != NEW.email THEN
       SET NEW.email_update_count = OLD.email_update_count + 1;
     END IF;
   END;

   correctly uses BEFORE UPDATE, compares emails, and sets NEW value.

   CREATE TRIGGER trg_email_update
   AFTER UPDATE ON users
   FOR EACH ROW
   BEGIN
     IF OLD.email != NEW.email THEN
       UPDATE users SET email_update_count = email_update_count + 1 WHERE id = NEW.id;
     END IF;
   END;

   tries to update the same table in AFTER UPDATE, causing recursion.

   CREATE TRIGGER trg_email_update
   AFTER UPDATE ON users
   FOR EACH ROW
   BEGIN
     SET NEW.email_update_count = OLD.email_update_count + 1;
   END;

   tries to set NEW in AFTER UPDATE (not allowed).

   CREATE TRIGGER trg_email_update
   AFTER UPDATE ON users
   FOR EACH ROW
   BEGIN
     IF OLD.email = NEW.email THEN
       UPDATE users SET email_update_count = email_update_count + 1 WHERE id = NEW.id;
     END IF;
   END;

   updates count when emails are equal (wrong logic).

4. Final Answer:

   BEFORE UPDATE trigger that sets NEW.email_update_count if OLD.email != NEW.email -> Option A

5. Quick Check:

   Modify NEW in BEFORE UPDATE triggers [OK]

Hint: Change NEW only in BEFORE UPDATE, check OLD vs NEW values [OK]

Common Mistakes:

• Trying to modify NEW in AFTER UPDATE triggers
• Updating the same table inside AFTER UPDATE causing recursion
• Wrong condition logic for email change