Trigger best practices and limitations in MySQL - Mini Project: Build & Apply

Trigger Best Practices and Limitations in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to keep track of changes made to the books table by automatically recording updates in a separate books_audit table. This helps you monitor changes and maintain data integrity.

🎯 Goal: Build a MySQL trigger that logs updates on the books table into the books_audit table, following best practices and understanding limitations of triggers.

📋 What You'll Learn

Create a books table with columns book_id (INT, primary key), title (VARCHAR(100)), and price (DECIMAL(5,2))

Create a books_audit table with columns audit_id (INT, primary key, auto-increment), book_id (INT), old_price (DECIMAL(5,2)), new_price (DECIMAL(5,2)), and changed_at (TIMESTAMP)

Create a BEFORE UPDATE trigger on the books table that inserts a record into books_audit only if the price changes

Use proper trigger syntax and avoid common pitfalls such as recursive triggers or complex logic inside the trigger

💡 Why This Matters

🌍 Real World

Triggers help automate tasks like auditing changes, enforcing business rules, and maintaining data integrity without manual intervention.

💼 Career

Database developers and administrators use triggers to ensure data consistency and to track changes for compliance and debugging.

1

Create the books and books_audit tables

Create a table called books with columns book_id as INT primary key, title as VARCHAR(100), and price as DECIMAL(5,2). Also create a table called books_audit with columns audit_id as INT primary key auto-increment, book_id as INT, old_price as DECIMAL(5,2), new_price as DECIMAL(5,2), and changed_at as TIMESTAMP.

MySQL

-- Create the books table
-- Create the books_audit table

Need a hint?

Use CREATE TABLE statements with the exact column names and types as specified.

2

Set up a helper variable to check price changes

Declare a variable inside the trigger to check if the price has changed by comparing OLD.price and NEW.price. This will help decide if an audit record should be inserted.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Declare a variable inside the trigger to check price change
-- Your code here

Need a hint?

Use DECLARE to create a boolean variable and SET to assign the comparison result.

3

Insert audit record only if price changed

Inside the trigger, write an IF statement that inserts a new row into books_audit with book_id, old_price, and new_price only if the price_changed variable is true.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_books_update
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
  DECLARE price_changed BOOLEAN;
  SET price_changed = (OLD.price  NEW.price);
  -- Insert audit record if price_changed is true
  -- Your code here
END$$
DELIMITER ;

Need a hint?

Use an IF block to conditionally insert into books_audit.

4

Complete the trigger with proper delimiter usage

Ensure the trigger ends with END and the delimiter is reset to ; after the trigger definition to avoid syntax errors.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_books_update
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
  DECLARE price_changed BOOLEAN;
  SET price_changed = (OLD.price  NEW.price);
  IF price_changed THEN
    INSERT INTO books_audit (book_id, old_price, new_price) VALUES (OLD.book_id, OLD.price, NEW.price);
  END IF;
  -- End the trigger and reset delimiter
  -- Your code here
END$$
DELIMITER ;

Need a hint?

Remember to close the trigger block with END and reset the delimiter to ;.