How to Create a BEFORE Trigger in PostgreSQL

In PostgreSQL, you create a BEFORE trigger by first writing a trigger function using CREATE FUNCTION and then attaching it to a table with CREATE TRIGGER specifying BEFORE INSERT, UPDATE, or DELETE. The trigger runs your function before the specified event happens on the table.

Syntax

The BEFORE trigger in PostgreSQL requires two steps: defining a trigger function and then creating the trigger itself.

• CREATE FUNCTION: Defines the function that runs before the event.
• CREATE TRIGGER: Links the function to a table and specifies when it runs (BEFORE INSERT, UPDATE, or DELETE).

sql

CREATE FUNCTION function_name() RETURNS trigger AS $$
BEGIN
  -- trigger logic here
  RETURN NEW; -- or RETURN OLD for DELETE
END;
$$ LANGUAGE plpgsql;

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

Example

This example creates a BEFORE INSERT trigger that automatically sets a column created_at to the current timestamp before a new row is inserted.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP
);

CREATE FUNCTION set_created_at() RETURNS trigger AS $$
BEGIN
  NEW.created_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_created_at();

-- Insert a row to see the trigger in action
INSERT INTO users (name) VALUES ('Alice');

-- Check the inserted row
SELECT * FROM users;

Output

 id | name  |       created_at       
----+-------+------------------------
  1 | Alice | 2024-06-01 12:00:00+00
(1 row)

Common Pitfalls

• Forgetting to RETURN NEW or RETURN OLD in the trigger function causes errors or no changes.
• Using BEFORE triggers but not specifying FOR EACH ROW when row-level behavior is needed.
• Not matching the trigger event (INSERT, UPDATE, DELETE) with the function logic.
• Trying to modify the table in a way that causes recursive trigger calls without safeguards.

sql

/* Wrong: Missing RETURN NEW */
CREATE FUNCTION bad_trigger() RETURNS trigger AS $$
BEGIN
  NEW.created_at := NOW();
  -- Missing RETURN NEW here
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

/* Right: Always return NEW or OLD */
CREATE FUNCTION good_trigger() RETURNS trigger AS $$
BEGIN
  NEW.created_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Remember these key points when creating BEFORE triggers in PostgreSQL:

• Trigger functions must return trigger type and return NEW or OLD.
• BEFORE triggers run before the event, allowing you to modify data.
• Use FOR EACH ROW for row-level triggers or FOR EACH STATEMENT for statement-level.
• Attach triggers to tables with CREATE TRIGGER specifying event and timing.

Key Takeaways

Create a trigger function with RETURN NEW or OLD to define BEFORE trigger behavior.

Attach the function to a table using CREATE TRIGGER with BEFORE and event type.

BEFORE triggers let you change data before it is inserted, updated, or deleted.

Always specify FOR EACH ROW for row-level triggers to affect individual rows.

Test triggers carefully to avoid infinite loops or missing RETURN statements.