Why triggers are needed in SQL - See It in Action

Understanding Why Triggers Are Needed in SQL

📖 Scenario: You are managing a small online bookstore database. You want to keep track of every time a book's price changes so you can analyze pricing trends later.

🎯 Goal: Build a simple SQL trigger that automatically records price changes in a separate table whenever the price of a book is updated.

📋 What You'll Learn

Create a table called books with columns book_id, title, and price

Create a table called price_changes with columns change_id, book_id, old_price, new_price, and change_date

Create a trigger called log_price_change that fires after an update on books

The trigger should insert a record into price_changes only if the price has changed

💡 Why This Matters

🌍 Real World

In real businesses, triggers help keep audit logs, enforce rules, and automate updates without extra application code.

💼 Career

Database administrators and developers use triggers to maintain data integrity and automate workflows in many industries.

1

Create the books table

Create a table called books with columns book_id as an integer primary key, title as text, and price as a decimal number.

SQL

-- Create the books 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, book_id as integer, old_price and new_price as decimal numbers, and change_date as a timestamp.

SQL

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);
-- Create the price_changes table
-- Your code here

Need a hint?

Use SERIAL for auto-incrementing change_id and set default for change_date.

3

Create the trigger function to log price changes

Create a trigger function called log_price_change_func that inserts a record into price_changes with the book_id, old_price, new_price, and current timestamp, but only if the price has changed.

SQL

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);

CREATE TABLE price_changes (
    change_id SERIAL PRIMARY KEY,
    book_id INT,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the trigger function
-- Your code here

Need a hint?

Use a trigger function with IF to check price change and insert accordingly.

4

Create the trigger to call the function after update

Create a trigger called log_price_change that fires AFTER UPDATE on the books table and calls the log_price_change_func function.

SQL

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(10, 2)
);

CREATE TABLE price_changes (
    change_id SERIAL PRIMARY KEY,
    book_id INT,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_price_change_func() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price  OLD.price THEN
        INSERT INTO price_changes(book_id, old_price, new_price, change_date)
        VALUES (NEW.book_id, OLD.price, NEW.price, CURRENT_TIMESTAMP);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER UPDATE and FOR EACH ROW.