Row Level vs Statement Level Trigger in PostgreSQL: Key Differences

In PostgreSQL, a row-level trigger fires once for each row affected by a query, while a statement-level trigger fires once per SQL statement regardless of how many rows are affected. Row-level triggers allow detailed row-specific actions, whereas statement-level triggers handle broader tasks after the entire statement completes.

Quick Comparison

Here is a quick comparison of row-level and statement-level triggers in PostgreSQL based on key factors.

Factor	Row-Level Trigger	Statement-Level Trigger
Execution Frequency	Fires once per affected row	Fires once per SQL statement
Timing	Before or after each row operation	Before or after the entire statement
Access to Data	Can access individual row data via NEW and OLD	Cannot access individual row data
Use Case	Row-specific validation or modification	Summary actions or logging after statement
Performance Impact	Higher overhead if many rows affected	Lower overhead for bulk operations

Key Differences

Row-level triggers execute once for every row that the triggering SQL statement affects. This means if an UPDATE modifies 100 rows, the trigger runs 100 times, each time with access to the specific row's data through NEW (new row state) and OLD (old row state) variables. This allows precise control and validation on a per-row basis.

In contrast, statement-level triggers execute only once per SQL statement, regardless of how many rows are affected. They do not have access to individual row data because they run outside the context of any single row. These triggers are useful for actions that should happen once per statement, such as logging or updating summary tables.

Because row-level triggers run multiple times for multi-row operations, they can add more overhead compared to statement-level triggers. Choosing between them depends on whether you need row-specific logic or a single action per statement.

Code Comparison

This example shows a row-level trigger that logs each updated row's ID.

sql

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);

CREATE TABLE product_log (product_id INT, action TEXT);

CREATE OR REPLACE FUNCTION log_product_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO product_log(product_id, action) VALUES (NEW.id, 'updated');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_update_row_trigger
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_update();

-- Example update
UPDATE products SET price = price * 1.1 WHERE price < 100;

Output

For each updated product row, a new entry is inserted into product_log with the product_id and 'updated' action.

Statement-Level Equivalent

This example shows a statement-level trigger that logs once per update statement without row details.

sql

CREATE OR REPLACE FUNCTION log_product_update_statement() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO product_log(product_id, action) VALUES (NULL, 'products updated');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_update_statement_trigger
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_product_update_statement();

-- Example update
UPDATE products SET price = price * 1.1 WHERE price < 100;

Output

One entry is inserted into product_log with NULL product_id and 'products updated' action per update statement.

When to Use Which

Choose row-level triggers when you need to inspect or modify each affected row individually, such as enforcing complex validations or logging detailed changes.

Choose statement-level triggers when you want to perform an action once per SQL statement, like updating summary data, auditing, or logging without needing row-specific details.

For performance, prefer statement-level triggers if you do not need row-level detail, especially on large batch operations.

Key Takeaways

Row-level triggers fire once per affected row and can access row data via NEW and OLD.

Statement-level triggers fire once per SQL statement and cannot access individual row data.

Use row-level triggers for detailed row-specific logic and statement-level for summary or logging tasks.

Row-level triggers can impact performance on large updates due to multiple executions.

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