How to Create Trigger in PostgreSQL: Syntax and Example

In PostgreSQL, you create a trigger using CREATE TRIGGER statement that links a trigger function to a table event like INSERT, UPDATE, or DELETE. First, define a trigger function with CREATE FUNCTION, then create the trigger specifying when it should fire.

Syntax

The CREATE TRIGGER statement connects a trigger function to a table event. It has these parts:

• trigger_name: Name you give to the trigger.
• trigger_time: When the trigger runs, either BEFORE or AFTER the event.
• event: The table event that fires the trigger, like INSERT, UPDATE, or DELETE.
• ON table_name: The table the trigger watches.
• FOR EACH ROW: Runs the trigger for each affected row.
• EXECUTE FUNCTION function_name(): Calls the trigger function.

sql

CREATE TRIGGER trigger_name
  { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
  ON table_name
  FOR EACH ROW
  EXECUTE FUNCTION function_name();

Example

This example creates a trigger that logs inserts into a table called users. It first defines a trigger function that inserts a log record, then creates the trigger to call this function after each insert.

sql

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

CREATE TABLE user_log (
  log_id SERIAL PRIMARY KEY,
  user_id INT,
  action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_user_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_log(user_id) VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_user_insert
  AFTER INSERT ON users
  FOR EACH ROW
  EXECUTE FUNCTION log_user_insert();

-- Insert a user to test
INSERT INTO users(name) VALUES ('Alice');

-- Check the log
SELECT * FROM user_log;

Output

 log_id | user_id |        action_time        
--------+---------+---------------------------
      1 |       1 | 2024-06-01 12:00:00+00
(1 row)

Common Pitfalls

Common mistakes when creating triggers include:

• Not creating the trigger function before the trigger itself.
• Forgetting to RETURN NEW or RETURN OLD in the trigger function, which is required for row-level triggers.
• Using FOR EACH STATEMENT when you meant FOR EACH ROW, which changes how many times the trigger runs.
• Trigger functions must be written in a supported language like plpgsql.

sql

/* Wrong: Missing RETURN NEW */
CREATE OR REPLACE FUNCTION bad_trigger() RETURNS TRIGGER AS $$
BEGIN
  -- some logic
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

/* Right: Include RETURN NEW */
CREATE OR REPLACE FUNCTION good_trigger() RETURNS TRIGGER AS $$
BEGIN
  -- some logic
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Part	Description	Example
trigger_name	Name of the trigger	after_user_insert
trigger_time	When trigger fires	BEFORE or AFTER
event	Table event that fires trigger	INSERT, UPDATE, DELETE
table_name	Table to watch	users
for_each	Row or statement level	FOR EACH ROW
function_name	Trigger function to call	log_user_insert()

Key Takeaways

Always create the trigger function before creating the trigger.

Use RETURN NEW or RETURN OLD in trigger functions for row-level triggers.

Specify WHEN (BEFORE or AFTER) and event (INSERT, UPDATE, DELETE) clearly.

FOR EACH ROW runs the trigger for every affected row; FOR EACH STATEMENT runs once per statement.

Trigger functions must be written in a supported language like plpgsql.