Row-level and Statement-level Triggers: FOR EACH ROW vs FOR EACH STATEMENT

If you've ever run into a situation where you need to do something for every row during a bulk data update, or just once for the whole table, you probably faced the dilemma: how should you actually do this? PostgreSQL gives you two options: row-level triggers and statement-level triggers. Knowing when to use each approach is super important for designing your database right, optimizing its performance, and avoiding bugs. Let's break it down!

Triggers that work at the row level (FOR EACH ROW) fire every single time for each row affected by an INSERT, UPDATE, or DELETE operation. That means if your SQL query touches 100 rows, the trigger will run 100 times.

When should you use them?

Row-level triggers are handy if you need to handle each changed row individually. For example:

• Logging changes for every row.
• Automatically updating related data for each row.

Example: Logging changes for every row

Let's say we've got a students table:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

We want to log every row that gets updated in this table into a separate students_log table:

CREATE TABLE students_log (
    log_id SERIAL PRIMARY KEY,
    student_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Function for logging changes:

CREATE OR REPLACE FUNCTION log_student_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_log(student_id, old_name, new_name, changed_at)
    VALUES (OLD.id, OLD.name, NEW.name, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Creating the FOR EACH ROW trigger:

CREATE TRIGGER student_update_logger
AFTER UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_update();

Testing:

UPDATE students
SET name = 'Ivan Ivanov'
WHERE id = 1;

After running this query, you'll see a record with the change details in the students_log table.

Statement-level triggers (FOR EACH STATEMENT)

Statement-level triggers (FOR EACH STATEMENT) fire just once for the whole SQL query, no matter how many rows are affected. If your query updates 100 rows, the trigger only runs once.

Statement-level triggers are useful if you want to:

• Do something just once for the whole operation.
• Work with aggregated data or do calculations for the whole table.

Example: Updating a change counter

Let's say we've got a change counter table for the students table:

CREATE TABLE students_changes_log (
    total_changes INT DEFAULT 0
);
INSERT INTO students_changes_log(total_changes) VALUES (0);

We want to bump up this counter every time an UPDATE happens in the students table.

Function to update the counter:

CREATE OR REPLACE FUNCTION increment_changes_counter()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE students_changes_log
    SET total_changes = total_changes + 1;
    RETURN NULL; -- Statement-level triggers don't return rows
END;
$$ LANGUAGE plpgsql;

Creating the FOR EACH STATEMENT trigger:

CREATE TRIGGER update_changes_counter
AFTER UPDATE ON students
FOR EACH STATEMENT
EXECUTE FUNCTION increment_changes_counter();

Testing:

UPDATE students
SET age = age + 1
WHERE age < 20;

After running the query, the trigger will fire just once, and the change counter will go up by one.

Comparing FOR EACH ROW and FOR EACH STATEMENT

When to use FOR EACH ROW and FOR EACH STATEMENT?

Use FOR EACH ROW if:

1. You want the trigger to run for every row.
2. Your logic needs to be tied to changes in specific rows.
3. You need access to OLD and NEW data for each row.

Example: Logging changes in a table or auto-creating related records.

Use FOR EACH STATEMENT if:

1. You want to do something just once for the whole operation.
2. The trigger logic doesn't depend on changes to specific rows.
3. Performance is critical and you don't want the trigger firing a ton of times.

Example: Updating counters, calculating table metadata.

Common mistakes and important points

Picking the right trigger type isn't always obvious, so here's what you should keep in mind:

1. One of the most common mistakes is trying to use OLD and NEW data in a FOR EACH STATEMENT trigger. That'll throw an error, since those variables are only available in row-level triggers.
2. Row-level triggers (FOR EACH ROW) can really slow things down if your query hits a lot of rows. Always think about performance.
3. Be careful with possible trigger recursion. For example, if a trigger changes data in the same table, you could end up in an infinite loop.