How to Create Trigger Function in PostgreSQL: Simple Guide

In PostgreSQL, you create a trigger function using CREATE FUNCTION with a special return type TRIGGER. Then, you attach this function to a table using CREATE TRIGGER to run it automatically on events like INSERT or UPDATE.

Syntax

The trigger function is created with CREATE FUNCTION and must return TRIGGER. It uses PL/pgSQL or another procedural language. The trigger itself is created with CREATE TRIGGER specifying when it fires.

• CREATE FUNCTION: Defines the trigger function.
• RETURNS TRIGGER: Required return type for trigger functions.
• LANGUAGE plpgsql: Common language for writing trigger logic.
• CREATE TRIGGER: Attaches the function to a table and event.
• BEFORE/AFTER: When the trigger runs relative to the event.
• INSERT/UPDATE/DELETE: The event that fires the trigger.

sql

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

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 function that logs inserts on a table called employees. The trigger fires after insert and inserts a record into a log table.

sql

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

CREATE TABLE employee_log (
  log_id SERIAL PRIMARY KEY,
  employee_id INT,
  action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION log_employee_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employee_log(employee_id) VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_employee_insert
  AFTER INSERT ON employees
  FOR EACH ROW
  EXECUTE FUNCTION log_employee_insert();

-- Test insert
INSERT INTO employees(name) VALUES ('Alice');

-- Check log
SELECT * FROM employee_log;

Output

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

Common Pitfalls

Common mistakes when creating trigger functions include:

• Not returning NEW or OLD properly, which can cause errors or no changes.
• Using BEFORE triggers but forgetting to modify NEW row if needed.
• Forgetting to specify FOR EACH ROW if row-level trigger is intended.
• Writing trigger logic that causes infinite loops by updating the same table without precautions.

sql

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

/* Right: Always return NEW or OLD */
CREATE FUNCTION good_trigger() RETURNS TRIGGER AS $$
BEGIN
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Keyword	Description
CREATE FUNCTION	Defines the trigger function with return type TRIGGER
RETURNS TRIGGER	Specifies the function returns a trigger type
LANGUAGE plpgsql	Sets the procedural language for the function
CREATE TRIGGER	Attaches the function to a table and event
BEFORE/AFTER	When the trigger runs relative to the event
INSERT/UPDATE/DELETE	The event that fires the trigger
FOR EACH ROW	Runs trigger once per affected row

Key Takeaways

Trigger functions in PostgreSQL must return type TRIGGER and use RETURN NEW or OLD.

Attach trigger functions to tables with CREATE TRIGGER specifying event and timing.

Use BEFORE or AFTER to control when the trigger runs relative to the event.

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

Avoid infinite loops by careful trigger logic and not updating the same table without checks.