Trigger for data validation in PostgreSQL

Introduction

Triggers help check data automatically when you add or change it. This keeps your data correct and clean.

When you want to make sure a new entry meets rules before saving.

When you want to check data changes and stop wrong updates.

When you want to log or fix data automatically after changes.

When you want to enforce complex rules that normal constraints can't handle.

Syntax

PostgreSQL

CREATE FUNCTION function_name() RETURNS trigger AS $$
BEGIN
  -- validation logic here
  IF (condition fails) THEN
    RAISE EXCEPTION 'Error message';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

The function runs before or after data changes depending on trigger timing.

Use RAISE EXCEPTION to stop wrong data from being saved.

Examples

This trigger checks that the age is not negative before saving a user.

PostgreSQL

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

CREATE TRIGGER validate_age
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION check_age();

This trigger ensures the email has a basic valid format before saving.

PostgreSQL

CREATE FUNCTION check_email() RETURNS trigger AS $$
BEGIN
  IF NEW.email NOT LIKE '%@%.%' THEN
    RAISE EXCEPTION 'Invalid email format';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_email
BEFORE INSERT OR UPDATE ON contacts
FOR EACH ROW EXECUTE FUNCTION check_email();

Sample Program

This example creates a table for employees and a trigger to check that salary is not negative. The first insert works, the second fails with an error.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  salary NUMERIC NOT NULL
);

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

CREATE TRIGGER validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION check_salary();

-- Try inserting a valid row
INSERT INTO employees (name, salary) VALUES ('Alice', 5000);

-- Try inserting an invalid row
INSERT INTO employees (name, salary) VALUES ('Bob', -1000);

Important Notes

Triggers run automatically and can stop bad data with errors.

Use BEFORE triggers to check or change data before saving.

Always test triggers carefully to avoid blocking valid data.

Summary

Triggers help check data automatically when inserting or updating.

Use RAISE EXCEPTION inside trigger functions to stop wrong data.

Triggers run BEFORE or AFTER data changes depending on need.