BEFORE trigger execution in SQL - Mini Project: Build & Apply

Create a BEFORE Trigger to Validate Data

📖 Scenario: You are managing a small online store database. You want to make sure that no product can be added with a negative price. To do this, you will create a BEFORE trigger that checks the price before inserting a new product.

🎯 Goal: Build a BEFORE trigger on the products table that prevents inserting a product with a negative price by raising an error.

📋 What You'll Learn

Create a products table with columns id (integer), name (text), and price (numeric).

Create a BEFORE INSERT trigger named check_price_before_insert on the products table.

The trigger should check if the new price is less than 0.

If the price is negative, the trigger should raise an error to prevent the insert.

💡 Why This Matters

🌍 Real World

Triggers help enforce business rules automatically in databases, such as preventing invalid data entry.

💼 Career

Database developers and administrators use triggers to maintain data integrity and automate checks.

1

Create the products table

Create a table called products with columns id as integer, name as text, and price as numeric.

SQL

-- Create the products table with id, name, and price columns
-- Your code here

Need a hint?

Use CREATE TABLE statement with the specified columns and types.

2

Create the BEFORE INSERT trigger function

Create a trigger function called check_price_before_insert_func that checks if NEW.price is less than 0 and raises an exception with the message 'Price cannot be negative' if true.

SQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

-- Create the trigger function to check price before insert
-- Your code here

Need a hint?

Use CREATE FUNCTION with RETURNS trigger and write the logic inside BEGIN ... END.

3

Create the BEFORE INSERT trigger

Create a BEFORE INSERT trigger named check_price_before_insert on the products table that calls the function check_price_before_insert_func.

SQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

CREATE FUNCTION check_price_before_insert_func() RETURNS trigger AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the BEFORE INSERT trigger on products
-- Your code here

Need a hint?

Use CREATE TRIGGER with BEFORE INSERT ON products and EXECUTE FUNCTION.

4

Test the trigger by inserting data

Insert a product with id 1, name 'Pen', and price 2.5. Then try to insert a product with id 2, name 'Notebook', and price -5 to see the trigger prevent the insert.

SQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

CREATE FUNCTION check_price_before_insert_func() RETURNS trigger AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_price_before_insert
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION check_price_before_insert_func();

-- Insert a valid product and an invalid product
-- Your code here

Need a hint?

Use INSERT INTO products with the exact values given.