How to Create an AFTER Trigger in PostgreSQL

In PostgreSQL, you create an AFTER trigger by first defining a trigger function using CREATE FUNCTION and then attaching it to a table with CREATE TRIGGER specifying AFTER and the event (INSERT, UPDATE, DELETE). The trigger runs automatically after the specified event on the table.

Syntax

To create an AFTER trigger in PostgreSQL, you need two steps:

• Create a trigger function: This function contains the code to run after the event.
• Create the trigger: This links the function to a table and event, specifying it runs AFTER the event.

The trigger function must return TRIGGER type.

sql

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

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

Example

This example creates an AFTER INSERT trigger on a table employees. The trigger logs a message into a log_table whenever a new employee is added.

sql

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

CREATE TABLE log_table (
  log_id SERIAL PRIMARY KEY,
  message TEXT NOT NULL,
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION log_employee_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO log_table(message) VALUES ('New employee added: ' || NEW.name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- Insert a new employee to test
INSERT INTO employees(name) VALUES ('Alice');

-- Check log_table content
SELECT * FROM log_table;

Output

 log_id |          message           |          log_time          
--------+----------------------------+----------------------------
      1 | New employee added: Alice  | 2024-06-01 12:00:00.000000
(1 row)

Common Pitfalls

• Forgetting to return NEW or OLD: The trigger function must return NEW for INSERT/UPDATE or OLD for DELETE triggers, or the operation may fail.
• Using BEFORE instead of AFTER: AFTER triggers run after the event, so using BEFORE triggers changes when the code runs.
• Not specifying FOR EACH ROW: Without this, the trigger runs once per statement, not per row.
• Trigger function language: The function must be written in a supported language like plpgsql.

sql

/* Wrong: Missing RETURN NEW */
CREATE FUNCTION wrong_trigger() RETURNS trigger AS $$
BEGIN
  -- missing RETURN NEW
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

/* Correct: Returns NEW */
CREATE FUNCTION correct_trigger() RETURNS trigger AS $$
BEGIN
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Part	Description
CREATE FUNCTION	Defines the trigger function with trigger logic
RETURNS trigger	Specifies the function returns a trigger type
LANGUAGE plpgsql	Sets the function language to PL/pgSQL
CREATE TRIGGER	Creates the trigger on a table
AFTER INSERT/UPDATE/DELETE	Specifies the event after which trigger runs
FOR EACH ROW	Runs trigger once per affected row
EXECUTE FUNCTION	Calls the trigger function

Key Takeaways

Create an AFTER trigger by defining a trigger function and linking it with CREATE TRIGGER.

The trigger function must return NEW or OLD depending on the event.

AFTER triggers run after the data change event completes.

Use FOR EACH ROW to run the trigger for every affected row.

Always specify the trigger function language, usually plpgsql.