Right, let’s talk about timing. This isn’t about your morning coffee; it’s about whether your trigger fires before the main event or after it. The choice between BEFORE and AFTER isn’t just a preference—it fundamentally changes what you can and cannot do, and it all boils down to one thing: access to the NEW and OLD records.

Think of it like this: a BEFORE trigger is your bouncer at the club door. They can check your ID (NEW.column), decide you’re not wearing the right shoes, and turn you away (by raising an exception) before you even get in. They can even slip a fake ID into your pocket (change the values in NEW) before the database commits the actual row insert or update. An AFTER trigger, on the other hand, is the security camera in the parking lot. The event (the insert, update, or delete) has already happened. The camera can see what went down, it can log it, it can even call for backup based on what it sees, but it can’t stop the row from entering the database or change its values. The deed is done.

The Critical Distinction: Mutability

This is the single most important concept to grasp. In a BEFORE trigger, the NEW record is mutable. You can change its values. This is your chance to sanitize input, set default values for a column the application didn’t bother with, or forcibly uppercase a username. The database will use whatever values are in NEW when the trigger finishes.

In an AFTER trigger, both NEW and OLD are immutable. They are read-only snapshots of the data. You can look, but you cannot touch. Trying to assign a value to NEW.some_column in an AFTER trigger will get you a very stern error message. Its job is auditing, cascading changes, or sending notifications, not data manipulation.

-- A BEFORE trigger can change the data
CREATE OR REPLACE FUNCTION before_insert_uppercase_username()
RETURNS TRIGGER AS $$
BEGIN
    -- Mutating NEW is allowed here. This changes the data that will be inserted.
    NEW.username := UPPER(NEW.username);
    RETURN NEW; -- The modified row is what gets saved
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ensure_uppercase_username
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION before_insert_uppercase_username();

-- An AFTER trigger CANNOT change the data. This will FAIL.
CREATE OR REPLACE FUNCTION after_insert_log_attempt()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit_log (user_id, action, timestamp)
    VALUES (NEW.id, 'USER_CREATED', NOW());
    
    -- The following line would cause a runtime error. Don't do it.
    -- NEW.username = 'ThisIsReadOnly'; 
    
    RETURN NEW; -- For AFTER triggers, RETURN NEW is still required but its values are ignored.
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_user_creation
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION after_insert_log_attempt();

Choosing Your Moment: Use Cases

So, when do you use which? The rule of thumb is simple, yet I see people get it wrong all the time.

Use a BEFORE trigger when:

  • You need to modify the data being written (NEW.column = 'some_value').
  • You need to validate data and potentially abort the operation if it’s invalid (RAISE EXCEPTION).

Use an AFTER trigger when:

  • You need guaranteed final state. The operation succeeded, the row is definitively there (or gone), and you need to react to that fact.
  • You need to make changes to other tables (audit logs, cascading updates, updating aggregate tables). Doing this in a BEFORE trigger is a bad smell; what if the main transaction fails and rolls back? You’d have logged an action that never happened. Doing it AFTER ensures the action was truly committed.
  • You need access to system-generated values (like a SERIAL primary key) for use in other operations. In a BEFORE trigger, the NEW.id might still be null; in an AFTER trigger, it’s guaranteed to be populated.

The OLD Record and DELETE Operations

This one trips people up. For INSERT operations, there is no OLD record. It’s null. Trying to reference it will cause an error. Conversely, for DELETE operations, there is no NEW record. It’s null. This makes perfect sense—you’re deleting an existing row (OLD) and not creating a new one.

The designers got this one right. The logic is clean: OLD represents the row being updated or deleted. NEW represents the row being inserted or the updated version of the row.

The Gotcha: Statement-Level Triggers

Here’s the part where the abstraction leaks, and it’s crucial. Everything I’ve said so far about NEW and OLD applies to row-level triggers (FOR EACH ROW). Statement-level triggers (FOR EACH STATEMENT) are a different beast entirely.

In a statement-level trigger, there are no NEW or OLD records. The trigger fires once for the entire SQL statement, regardless of whether it affected 0, 1, or 10,000 rows. You cannot access the data of individual rows within them. Their use is far more limited, typically for controlling entire operations or maintaining summary tables where you don’t care about the individual changes, just the fact that a change occurred.

Trying to reference NEW or OLD inside a statement-level trigger is a one-way ticket to a compilation error. It’s a classic mistake, so if your trigger function suddenly can’t see its favorite variables, check if you forgot to specify FOR EACH ROW.

Choose wisely. Use BEFORE to play bouncer and alter reality before it’s written. Use AFTER to be the historian, faithfully recording what actually transpired. And always, always know which one you’re using.