Trigger for data validation in PostgreSQL - Mini Project: Build & Apply

Trigger for Data Validation in PostgreSQL

📖 Scenario: You are managing a customer database for an online store. To keep data clean, you want to make sure that no customer can be added with an invalid email address.

🎯 Goal: Create a PostgreSQL trigger that validates the email format before inserting a new customer record. If the email is invalid, the insertion should be stopped.

📋 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 validate_email that checks if the email contains an '@' character.

Create a trigger called email_check_trigger that calls validate_email before inserting a new row into customers.

If the email is invalid, the trigger function should raise an exception to prevent the insert.

💡 Why This Matters

🌍 Real World

Triggers help keep data clean automatically by validating data before it enters the database, reducing errors and bad data.

💼 Career

Database developers and administrators use triggers to enforce business rules and data integrity without relying on application code.

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

-- Your code here to create the customers table

Need a hint?

Use SERIAL PRIMARY KEY for the id column to auto-increment.

2

Create the trigger function validate_email

Create a trigger function called validate_email in PL/pgSQL that checks if NEW.email contains the '@' character. If not, raise an exception with the message 'Invalid email address'.

PostgreSQL

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

-- Your code here to create the validate_email trigger function

Need a hint?

Use POSITION to check if '@' is in the email string.

3

Create the trigger email_check_trigger

Create a trigger called email_check_trigger that calls the validate_email function before inserting a new row into the customers table.

PostgreSQL

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

CREATE OR REPLACE FUNCTION validate_email() RETURNS TRIGGER AS $$
BEGIN
    IF POSITION('@' IN NEW.email) = 0 THEN
        RAISE EXCEPTION 'Invalid email address';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Your code here to create the email_check_trigger trigger

Need a hint?

Use BEFORE INSERT ON customers FOR EACH ROW EXECUTE FUNCTION validate_email() to create the trigger.

4

Test the trigger by inserting valid and invalid emails

Insert a valid customer with email '[e-mail]' and an invalid customer with email 'bobexample.com' to test the trigger. Use the exact insert statements shown.

PostgreSQL

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

CREATE OR REPLACE FUNCTION validate_email() RETURNS TRIGGER AS $$
BEGIN
    IF POSITION('@' IN NEW.email) = 0 THEN
        RAISE EXCEPTION 'Invalid email address';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER email_check_trigger
BEFORE INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION validate_email();

-- Your code here to insert test customers

Need a hint?

Use two INSERT INTO customers (name, email) VALUES (...) statements with the exact emails given.