How to Create Row Level Trigger in PostgreSQL: Syntax and Example

In PostgreSQL, create a row level trigger by defining a trigger function with CREATE FUNCTION and then attaching it to a table using CREATE TRIGGER with the FOR EACH ROW clause. This ensures the trigger fires once for every row affected by an insert, update, or delete operation.

Syntax

A row level trigger in PostgreSQL requires two parts: a trigger function and the trigger itself.

• Trigger function: A special function that runs when the trigger fires. It must return trigger type.
• CREATE TRIGGER: Defines when and how the trigger runs, specifying FOR EACH ROW to make it row level.

sql

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

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

Example

This example creates a row level trigger that logs changes to a table called employees whenever a row is updated.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  salary NUMERIC
);

CREATE TABLE employees_log (
  log_id SERIAL PRIMARY KEY,
  employee_id INT,
  old_salary NUMERIC,
  new_salary NUMERIC,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION log_salary_change() RETURNS trigger AS $$
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO employees_log(employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_update_trigger
  AFTER UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION log_salary_change();

-- Insert and update to test
INSERT INTO employees(name, salary) VALUES ('Alice', 50000);
UPDATE employees SET salary = 55000 WHERE name = 'Alice';

-- Check log
SELECT * FROM employees_log;

Output

 log_id | employee_id | old_salary | new_salary |       changed_at       
--------+-------------+------------+------------+------------------------
      1 |           1 |     50000  |     55000  | 2024-06-01 12:00:00+00
(1 row)

Common Pitfalls

• Forgetting FOR EACH ROW makes the trigger statement-level, not row-level.
• Not returning NEW or OLD in the trigger function causes errors or unexpected behavior.
• Using BEFORE triggers when you want to log changes after they happen (use AFTER instead).
• Trigger functions must be written in a supported language like plpgsql.

sql

/* Wrong: Missing FOR EACH ROW */
CREATE TRIGGER wrong_trigger
  AFTER UPDATE ON employees
  EXECUTE FUNCTION log_salary_change();

/* Correct: Include FOR EACH ROW */
CREATE TRIGGER correct_trigger
  AFTER UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION log_salary_change();

Quick Reference

Part	Description
CREATE FUNCTION	Defines the trigger function with trigger logic
RETURNS trigger	Specifies the function returns a trigger type
FOR EACH ROW	Makes the trigger fire once per affected row
BEFORE/AFTER/INSTEAD OF	Specifies when the trigger runs relative to the event
INSERT/UPDATE/DELETE	Specifies which event activates the trigger
EXECUTE FUNCTION	Calls the trigger function when triggered

Key Takeaways

Use CREATE FUNCTION to define the trigger logic returning trigger type.

Attach the trigger to a table with CREATE TRIGGER and specify FOR EACH ROW for row-level firing.

Always return NEW or OLD in the trigger function to avoid errors.

Choose BEFORE or AFTER depending on when you want the trigger to run.

Test triggers with sample inserts or updates to verify behavior.