Why triggers are needed in PostgreSQL - See It in Action

Understanding Why Triggers Are Needed in PostgreSQL

📖 Scenario: Imagine you manage a small online store database. You want to keep track of every time a product's price changes, so you can analyze price trends later.

🎯 Goal: You will create a simple table to store products and a log table to record price changes automatically using a trigger. This will help you understand why triggers are useful in databases.

📋 What You'll Learn

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

Create a price_changes table to log product ID, old price, new price, and change time

Write a trigger function that inserts a record into price_changes whenever a product's price is updated

Create a trigger that calls the trigger function on price updates

💡 Why This Matters

🌍 Real World

Triggers are used in real databases to automate actions like logging changes, enforcing rules, or updating related data without extra manual steps.

💼 Career

Understanding triggers is important for database administrators and developers to maintain data integrity and automate workflows efficiently.

1

Create the products table

Write SQL to create a table called products with columns: id as serial primary key, name as text, and price as numeric.

PostgreSQL

-- Your code here to create the products table

Need a hint?

Use SERIAL PRIMARY KEY for id to auto-increment.

2

Create the price_changes log table

Write SQL to create a table called price_changes with columns: product_id as integer, old_price as numeric, new_price as numeric, and changed_at as timestamp with time zone.

PostgreSQL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);
-- Your code here to create the price_changes table

Need a hint?

Use TIMESTAMPTZ for the timestamp with time zone.

3

Write the trigger function to log price changes

Write a trigger function called log_price_change in PL/pgSQL that inserts into price_changes the product_id, old_price, new_price, and current timestamp when a product's price is updated.

PostgreSQL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

CREATE TABLE price_changes (
    product_id INTEGER,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMPTZ
);

-- Your code here to create the trigger function log_price_change

Need a hint?

Use OLD and NEW to access old and new row values inside the trigger function.

4

Create the trigger to call the function on price updates

Write SQL to create a trigger called price_update_trigger that fires AFTER UPDATE on the products table, calling the log_price_change function only when the price column changes.

PostgreSQL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

CREATE TABLE price_changes (
    product_id INTEGER,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION log_price_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_changes(product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Your code here to create the trigger price_update_trigger

Need a hint?

Use AFTER UPDATE OF price and WHEN (OLD.price IS DISTINCT FROM NEW.price) to trigger only on price changes.