BEFORE trigger behavior in PostgreSQL

Introduction

A BEFORE trigger lets you run some checks or changes before data is saved in the database. It helps keep data clean and correct.

You want to check or change data before it is inserted or updated.

You need to stop a wrong or unwanted change before it happens.

You want to automatically fill in missing information before saving.

You want to log or audit changes before they are made.

You want to modify data to a standard format before saving.

Syntax

PostgreSQL

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

BEFORE triggers run before the data change happens.

You can modify the data in the trigger before it is saved.

Examples

This trigger runs before a new user is added to check their age.

PostgreSQL

CREATE TRIGGER check_age
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION check_age_function();

This trigger updates a timestamp before an order is changed.

PostgreSQL

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_function();

Sample Program

This example creates a BEFORE INSERT trigger that stops adding users younger than 18.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age INT
);

CREATE OR REPLACE FUNCTION check_age_function()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.age < 18 THEN
    RAISE EXCEPTION 'Age must be 18 or older';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_age
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION check_age_function();

-- Try to insert a user with age 16
INSERT INTO users (name, age) VALUES ('Alice', 16);

Important Notes

BEFORE triggers can change the data by modifying the NEW record.

If a BEFORE trigger raises an error, the data change is stopped.

BEFORE triggers run once for each row affected.

Summary

BEFORE triggers run before data is inserted or updated.

They let you check or change data before saving.

You can stop bad data by raising errors in BEFORE triggers.