Row-level vs statement-level triggers in PostgreSQL

Introduction

Triggers help run extra actions automatically when data changes. Row-level triggers run once for each row changed. Statement-level triggers run once for the whole action.

When you want to check or change each row individually after an update.

When you want to log or audit all changes made by a single command at once.

When you want to enforce rules on every row inserted or deleted.

When you want to do a summary action after a batch update or delete.

When you want to avoid repeating the same action many times for each row.

Syntax

PostgreSQL

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

FOR EACH ROW means the trigger runs once per row changed.

FOR EACH STATEMENT means the trigger runs once per SQL command, no matter how many rows it affects.

Examples

This trigger runs after each row in the employees table is updated.

PostgreSQL

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_update();

This trigger runs once after any insert or delete command on the orders table, no matter how many rows changed.

PostgreSQL

CREATE TRIGGER audit_changes
AFTER INSERT OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION audit_order_changes();

Sample Program

This example shows two triggers on the products table. One runs after each row update and prints 'Row updated'. The other runs once after the whole update statement and prints 'Update statement executed'.

PostgreSQL

-- Create a simple table
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);

-- Create a function to count updated rows
CREATE OR REPLACE FUNCTION count_updates() RETURNS TRIGGER AS $$
DECLARE
  counter INTEGER := 0;
BEGIN
  IF TG_OP = 'UPDATE' THEN
    counter := counter + 1;
  END IF;
  RAISE NOTICE 'Row updated';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Row-level trigger: runs once per updated row
CREATE TRIGGER product_update_row
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION count_updates();

-- Statement-level trigger function
CREATE OR REPLACE FUNCTION statement_update_notice() RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'Update statement executed';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Statement-level trigger: runs once per update statement
CREATE TRIGGER product_update_statement
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION statement_update_notice();

-- Insert sample data
INSERT INTO products (name, price) VALUES ('Pen', 1.20), ('Notebook', 2.50);

-- Update data to see triggers in action
UPDATE products SET price = price + 1 WHERE id IN (1, 2);

Important Notes

Row-level triggers can slow down operations if many rows are affected because they run once per row.

Statement-level triggers are good for actions that only need to happen once per command.

Use RAISE NOTICE in PostgreSQL to see messages during trigger execution for learning or debugging.

Summary

Row-level triggers run once for each row affected by a command.

Statement-level triggers run once for the entire command, no matter how many rows change.

Choose the trigger type based on whether you need per-row or per-command actions.