Right, let’s get into the weeds on this one. The FOR EACH ROW vs. FOR EACH STATEMENT distinction is one of those things that seems trivial until you get it wrong and your trigger starts behaving like a hyperactive caffeinated squirrel instead of a precise database tool. Choosing the right one isn’t a matter of preference; it’s about what your logic needs to do.

The core of it is brutally simple:

  • A FOR EACH ROW trigger fires once for every single row affected by the operation. Update 1000 rows? Your trigger runs 1000 times.
  • A FOR EACH STATEMENT trigger fires once per SQL statement, no matter how many rows it touches. That same update of 1000 rows? Your statement-level trigger runs exactly once.

Think of it like a bulk package delivery. The FOR EACH ROW trigger is like a postal worker scanning every single box on the pallet. The FOR EACH STATEMENT trigger is the warehouse manager who just scans the master shipping manifest once for the entire pallet.

The OLD and NEW Special Variables

This is where the first major practical difference hits you in the face, and it’s why you can’t just swap one for the other.

In a row-level trigger (FOR EACH ROW), you get access to the OLD and NEW record variables. These are your golden tickets. They let you see the state of a row before the operation (OLD) and after the operation (NEW). You can inspect their values, change NEW values (in a BEFORE trigger), and make decisions based on the specific data of each row.

CREATE OR REPLACE FUNCTION log_user_email_change()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    -- This is a row-level trigger, so OLD and NEW exist for THIS row.
    IF OLD.email <> NEW.email THEN
        INSERT INTO user_audit_log (user_id, changed_field, old_value, new_value)
        VALUES (OLD.id, 'email', OLD.email, NEW.email);
    END IF;
    RETURN NEW; -- Remember, this is required for row-level triggers!
END;
$$;

CREATE TRIGGER trigger_log_email_change
    BEFORE UPDATE OF email ON users
    FOR EACH ROW -- Critical! We need per-row data.
    EXECUTE FUNCTION log_user_email_change();

Now, try to use OLD or NEW in a statement-level trigger. Go on, I dare you. The database will laugh at you and throw an error. Because a statement-level trigger fires after the entire statement is done, there is no single “current row.” There is no OLD or NEW. It’s like asking the warehouse manager what’s inside the third box on the pallet—they have no idea without opening the whole thing again.

When to Use Which (And Why You Usually Pick ROW)

You reach for FOR EACH STATEMENT when your logic is concerned with the aggregate effect of the operation, not the individual changes.

A classic use case is statement-level auditing. You don’t care which rows changed; you care that a user executed a DELETE statement against the invoices table at 3 AM. You just want to log the fact that it happened.

CREATE OR REPLACE FUNCTION log_table_access()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    -- No OLD or NEW here! We use TG_OP and TG_TABLE_NAME.
    INSERT INTO statement_audit_log (username, operation, table_name, timestamp)
    VALUES (current_user, TG_OP, TG_TABLE_NAME, now());
    RETURN NULL; -- It's an AFTER trigger, return value is ignored anyway.
END;
$$;

CREATE TRIGGER trigger_audit_invoices
    AFTER INSERT OR UPDATE OR DELETE ON invoices
    FOR EACH STATEMENT -- Fires once, no matter if 1 or 1000 rows are affected.
    EXECUTE FUNCTION log_table_access();

But let’s be honest, 95% of the triggers you’ll ever write will be FOR EACH ROW. Data validation, complex default values, maintaining summary counts, cascading changes—these all require looking at the data inside each row. That’s the bread and butter of trigger-based logic.

The Performance Elephant in the Room

This isn’t a trivial choice. Performance implications are massive. Issuing a single UPDATE statement that affects ten million rows? A FOR EACH ROW trigger will fire ten million times. If that trigger contains an INSERT into another table or a complex calculation, you’ve just turned a quick operation into a multi-hour nightmare. Your transaction will hold locks the entire time, grinding the entire system to a halt.

A statement-level trigger, in this scenario, would be blissfully efficient, firing exactly once. But again, it’s useless if you need per-row data.

The designers gave us a trade-off: granular data access vs. performance at scale. There’s no way around it. The best practice is to ask yourself brutally, “Do I need the data from each individual row?” If the answer is no, for the love of all that is holy, use FOR EACH STATEMENT. Your future self (and your DBA) will thank you. If you do need the data, make sure your row-level trigger code is as lean and mean as possible. Every millisecond of execution gets multiplied by the number of rows.