UPDATE trigger with OLD and NEW in SQL - Mini Project: Build & Apply

Create an UPDATE Trigger Using OLD and NEW

📖 Scenario: You manage a small library database. You want to keep track of every time a book's price changes. To do this, you will create a trigger that records the old price and the new price whenever an update happens.

🎯 Goal: Build an UPDATE trigger on the books table that uses OLD and NEW to log price changes into a price_changes table.

📋 What You'll Learn

Create a books table with columns id, title, and price.

Create a price_changes table with columns book_id, old_price, and new_price.

Create an UPDATE trigger on books that inserts a record into price_changes whenever the price changes.

Use OLD.price and NEW.price inside the trigger to get the old and new prices.

💡 Why This Matters

🌍 Real World

Tracking changes in data is important for audit logs, history, and debugging in real-world databases.

💼 Career

Database developers and administrators often write triggers to automate tasks and maintain data integrity.

1

Create the books table

Write SQL to create a table called books with columns: id as an integer primary key, title as text, and price as a decimal number.

SQL

-- Create the books table with id, title, and price columns
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns exactly as specified.

2

Create the price_changes table

Write SQL to create a table called price_changes with columns: book_id as integer, old_price as decimal, and new_price as decimal.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);

-- Create the price_changes table with book_id, old_price, and new_price columns
-- Your code here

Need a hint?

Define the price_changes table with the exact columns and types.

3

Start creating the UPDATE trigger

Write SQL to create a trigger named log_price_change that fires AFTER UPDATE on the books table.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);

CREATE TABLE price_changes (
    book_id INTEGER,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2)
);

-- Create an AFTER UPDATE trigger named log_price_change on books
-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER UPDATE ON books and FOR EACH ROW.

4

Complete the trigger to log price changes using OLD and NEW

Inside the trigger body, write SQL to insert into price_changes the book_id as NEW.id, old_price as OLD.price, and new_price as NEW.price. Only insert if the price has changed (compare OLD.price and NEW.price).

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);

CREATE TABLE price_changes (
    book_id INTEGER,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2)
);

CREATE TRIGGER log_price_change
AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    -- Insert into price_changes only if OLD.price is different from NEW.price
    -- Your code here
END;

Need a hint?

Use an IF statement to check if prices differ, then insert the old and new prices using OLD.price and NEW.price.