BEFORE trigger behavior in PostgreSQL - Mini Project: Build & Apply

Create a BEFORE Trigger in PostgreSQL

📖 Scenario: You are managing a customer database for a small business. You want to ensure that before any new customer is added, their email address is always stored in lowercase to maintain consistency.

🎯 Goal: Build a PostgreSQL BEFORE INSERT trigger that automatically converts the email field to lowercase before saving a new customer record.

📋 What You'll Learn

Create a table called customers with columns id (integer primary key), name (text), and email (text).

Create a trigger function called lowercase_email that converts the email field to lowercase.

Create a BEFORE INSERT trigger called before_insert_customers on the customers table that calls the lowercase_email function.

Ensure the trigger modifies the NEW.email value before the row is inserted.

💡 Why This Matters

🌍 Real World

Triggers help enforce data consistency automatically, such as formatting emails uniformly before saving.

💼 Career

Database triggers are common in backend development and database administration to maintain data integrity and automate tasks.

1

Create the customers table

Create a table called customers with columns id as an integer primary key, name as text, and email as text.

PostgreSQL

-- Create the customers table with id, name, and email columns
-- Your code here

Need a hint?

Use CREATE TABLE customers and define id SERIAL PRIMARY KEY, name TEXT, and email TEXT.

2

Create the trigger function lowercase_email

Create a trigger function called lowercase_email that returns a trigger and converts NEW.email to lowercase using LOWER(NEW.email). The function should return NEW.

PostgreSQL

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

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

Need a hint?

Define a function with CREATE OR REPLACE FUNCTION lowercase_email() RETURNS trigger. Use NEW.email := LOWER(NEW.email); and RETURN NEW;.

3

Create the BEFORE INSERT trigger before_insert_customers

Create a trigger called before_insert_customers that fires BEFORE INSERT on the customers table and calls the lowercase_email function.

PostgreSQL

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

CREATE OR REPLACE FUNCTION lowercase_email() RETURNS trigger AS $$
BEGIN
    NEW.email := LOWER(NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the BEFORE INSERT trigger before_insert_customers
-- Your code here

Need a hint?

Use CREATE TRIGGER before_insert_customers BEFORE INSERT ON customers FOR EACH ROW EXECUTE FUNCTION lowercase_email();

4

Verify the trigger modifies NEW.email before insert

Ensure the trigger function modifies NEW.email to lowercase before the row is inserted by confirming the trigger calls lowercase_email and the function returns NEW.

PostgreSQL

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

CREATE OR REPLACE FUNCTION lowercase_email() RETURNS trigger AS $$
BEGIN
    NEW.email := LOWER(NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_customers
BEFORE INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION lowercase_email();

-- Confirm the trigger function modifies NEW.email and returns NEW
-- Your code here

Need a hint?

Check that NEW.email is set to lowercase and RETURN NEW is present in the function, and the trigger calls this function before insert.