Trigger function creation in PostgreSQL - Mini Project: Build & Apply

Create a Trigger Function in PostgreSQL

📖 Scenario: You are managing a simple employee database. You want to keep track of when employee records are updated by automatically recording the update time.

🎯 Goal: Build a trigger function in PostgreSQL that updates a timestamp column whenever an employee record is modified.

📋 What You'll Learn

Create a table named employees with columns id, name, and last_modified

Create a trigger function named update_last_modified that sets last_modified to the current timestamp

Create a trigger named set_last_modified that calls the trigger function before any update on employees

💡 Why This Matters

🌍 Real World

Automatically tracking when records are updated is common in business databases to maintain data accuracy and audit trails.

💼 Career

Database developers and administrators often create triggers to enforce business rules and automate data management tasks.

1

Create the employees table

Create a table called employees with columns: id as serial primary key, name as varchar(100), and last_modified as timestamp without time zone.

PostgreSQL

-- Create the employees table with id, name, and last_modified columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Create the trigger function update_last_modified

Create a trigger function named update_last_modified that returns trigger and sets NEW.last_modified to NOW() before returning NEW.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    last_modified TIMESTAMP WITHOUT TIME ZONE
);

-- Create the trigger function update_last_modified
-- Your code here

Need a hint?

Use CREATE OR REPLACE FUNCTION with RETURNS TRIGGER and set NEW.last_modified = NOW().

3

Create the trigger set_last_modified

Create a trigger named set_last_modified on the employees table that fires BEFORE UPDATE and calls the trigger function update_last_modified.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    last_modified TIMESTAMP WITHOUT TIME ZONE
);

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger set_last_modified on employees BEFORE UPDATE
-- Your code here

Need a hint?

Use CREATE TRIGGER with BEFORE UPDATE ON employees and EXECUTE FUNCTION update_last_modified().

4

Verify the trigger function setup

Add a comment at the end of your code confirming the trigger set_last_modified is ready to update last_modified on employee updates.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    last_modified TIMESTAMP WITHOUT TIME ZONE
);

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

-- Add a comment confirming the trigger is set up
-- Your code here

Need a hint?

Add a simple comment line confirming the trigger setup.