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

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

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

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

Hint

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

Practice

1. Why are triggers needed in a PostgreSQL database?

easy

A. To automatically perform actions when data changes

B. To manually update data by user commands

C. To store large files outside the database

D. To create user accounts and manage permissions

Solution

1. Step 1: Understand the purpose of triggers

   Triggers run automatically when data changes, so they help automate tasks without manual intervention.

2. Step 2: Compare options to trigger function

   Options A, C, and D describe manual updates, file storage, and user management, which are not the main purpose of triggers.

3. Final Answer:

   To automatically perform actions when data changes -> Option A

4. Quick Check:

   Triggers automate tasks = To automatically perform actions when data changes [OK]

Hint: Triggers run automatically on data changes [OK]

Common Mistakes:

• Thinking triggers are for manual updates
• Confusing triggers with file storage
• Assuming triggers manage user permissions

2. Which of the following is the correct syntax to create a trigger in PostgreSQL?

easy

A. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name();

B. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name();

C. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name();

D. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name();

Solution

1. Step 1: Review PostgreSQL trigger syntax

   The correct syntax uses CREATE TRIGGER, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), table, FOR EACH ROW or STATEMENT, and EXECUTE FUNCTION.

2. Step 2: Check each option for syntax correctness

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); matches the correct syntax exactly. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name(); uses CALL instead of EXECUTE FUNCTION, which is invalid. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name(); uses EXECUTE PROCEDURE, deprecated in modern PostgreSQL. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name(); places ON table_name before timing and event, which is incorrect order.

3. Final Answer:

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); -> Option A

4. Quick Check:

   Correct trigger syntax = CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); [OK]

Hint: Use EXECUTE FUNCTION, not CALL or PROCEDURE [OK]

Common Mistakes:

• Using CALL instead of EXECUTE FUNCTION
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Placing ON table_name in wrong position

3. Given this trigger function and trigger, what will happen when a new row is inserted into orders table?

CREATE FUNCTION check_stock() RETURNS trigger AS $$ BEGIN IF NEW.quantity > stock THEN RAISE EXCEPTION 'Not enough stock'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER stock_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_stock();

medium

A. The trigger will delete the row if quantity is too high

B. The insert will fail if quantity is greater than stock

C. The trigger will update the stock automatically

D. The insert will always succeed regardless of quantity

Solution

1. Step 1: Analyze the trigger function logic

   The function checks if NEW.quantity is greater than stock. If yes, it raises an exception to stop the insert.

2. Step 2: Understand trigger timing and effect

   The trigger runs BEFORE INSERT on orders. If exception is raised, insert fails. Otherwise, it returns NEW row to proceed.

3. Final Answer:

   The insert will fail if quantity is greater than stock -> Option B

4. Quick Check:

   Exception raised on high quantity = The insert will fail if quantity is greater than stock [OK]

Hint: Exception stops insert if condition met [OK]

Common Mistakes:

• Assuming trigger updates stock automatically
• Thinking insert always succeeds
• Believing trigger deletes rows

4. You created this trigger but it does not run when you insert data:

CREATE TRIGGER log_insert AFTER INSERT ON sales EXECUTE FUNCTION log_sale();

What is the likely problem?

medium

A. AFTER INSERT triggers cannot be created

B. Trigger function name is incorrect

C. Missing FOR EACH ROW clause in trigger definition

D. log_sale() function must return void

Solution

1. Step 1: Check trigger syntax requirements

   PostgreSQL requires FOR EACH ROW or FOR EACH STATEMENT clause in CREATE TRIGGER statement.

2. Step 2: Identify missing clause in given trigger

   The trigger lacks FOR EACH ROW or FOR EACH STATEMENT, so it is invalid and will not run.

3. Final Answer:

   Missing FOR EACH ROW clause in trigger definition -> Option C

4. Quick Check:

   Triggers need FOR EACH ROW/STATEMENT clause [OK]

Hint: Always include FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Assuming AFTER INSERT triggers are disallowed
• Thinking function name causes trigger failure
• Believing trigger functions must return void

5. You want to keep a history of changes to the employees table automatically. Which trigger setup best achieves this?

hard

A. Create AFTER UPDATE trigger that drops employees table

B. Create AFTER DELETE trigger that updates employees table

C. Create BEFORE INSERT trigger that deletes old rows

D. Create BEFORE UPDATE trigger that inserts old row into history table

Solution

1. Step 1: Understand requirement to keep change history

   To keep history, old data must be saved before it changes or is deleted.

2. Step 2: Evaluate trigger options for history tracking

   Create BEFORE UPDATE trigger that inserts old row into history table uses BEFORE UPDATE trigger to save old row to history table, which is correct. Create AFTER DELETE trigger that updates employees table updates employees after delete, which is unrelated. Create BEFORE INSERT trigger that deletes old rows deletes old rows before insert, losing data. Create AFTER UPDATE trigger that drops employees table drops the table, which is destructive.

3. Final Answer:

   Create BEFORE UPDATE trigger that inserts old row into history table -> Option D

4. Quick Check:

   Save old data before update = Create BEFORE UPDATE trigger that inserts old row into history table [OK]

Hint: Save old row before update for history [OK]

Common Mistakes:

• Using AFTER DELETE to update main table
• Deleting old rows on insert
• Dropping table by mistake