Right, let’s talk about triggers. You’re about to give your database a nervous system, a way to react automatically to things that happen. It’s incredibly powerful, and with that power comes the responsibility not to create a Rube Goldberg machine of SQL that you can’t debug. The core of a trigger is telling it three things: when it should fire, what should make it fire, and what it should actually do.

The CREATE TRIGGER statement is where you wire this all up. It looks deceptively simple, but the choices you make here are everything.

The Anatomy of a Trigger Declaration

Here’s the basic skeleton. Don’t worry, we’ll put some meat on it.

CREATE TRIGGER trigger_name
    [BEFORE | AFTER] [INSERT | UPDATE | DELETE | TRUNCATE]
    ON table_name
    [FOR EACH ROW | FOR EACH STATEMENT]
    EXECUTE FUNCTION your_trigger_function_name();

Let’s dissect this, because each clause is a critical decision point.

Timing: The BEFORE or AFTER Dilemma

This isn’t just a sequence; it’s a question of authority and access.

  • BEFORE: The trigger fires before the operation even tries to happen. This is your chance to veto the operation (by raising an error) or to modify the data about to be written. Want to ensure a timestamp is always set to NOW(), no matter what the application sends? BEFORE INSERT is your guy. Need to validate something and scream if it’s invalid? Also BEFORE.

  • AFTER: The trigger fires after the operation has successfully completed. This is your chance to react to a fait accompli. The data is already in the table. This is perfect for logging, or for updating summaries or other tables based on this confirmed change. You can’t change the data that was just written here; it’s too late. The deed is done.

Mixing these up is a classic rookie mistake. Trying to modify the NEW row in an AFTER trigger is like trying to unbake a cake. It just leads to confusion and errors.

Event: What Are You Listening For?

This one’s straightforward: INSERT, UPDATE, DELETE, or TRUNCATE. You can also use OR to combine them, which is often very useful.

-- A trigger that fires for both inserts and updates
CREATE TRIGGER log_changes
    AFTER INSERT OR UPDATE
    ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_change();

A quick note on TRUNCATE: It’s a statement-level operation (more on that in a sec) and can only have BEFORE or AFTER statement-level triggers. You can’t have a FOR EACH ROW trigger on a TRUNCATE because, well, it doesn’t deal with rows individually. It just vaporizes the whole table.

Granularity: Row-Level vs. Statement-Level

This is the big one, the concept that causes the most head-scratching. The choice here fundamentally changes how your trigger function is called.

  • FOR EACH ROW: This is the default for most people. The trigger function will be called once for every single row that is affected by the operation. An UPDATE that modifies 10,000 rows? Your trigger function runs 10,000 times. Inside the function, you have access to the old row (OLD) and the new row (NEW) for that specific operation.

  • FOR EACH STATEMENT: The trigger function is called exactly once per statement, no matter how many rows it affects. That same UPDATE that changes 10,000 rows? A statement-level trigger runs once. Here’s the kicker: you do not have access to the OLD and NEW variables. How could you? They represent individual rows, and your trigger is firing for a whole statement that might have changed everything.

So why would you ever use statement-level? They’re perfect for tasks where you care that the operation happened, but not about every individual change. Think bulk post-processing or maintaining a coarse-grained audit log that just says “a mass update occurred at this time.”

Here’s a concrete example. Let’s say you have a purchases table and a user_totals table that caches their lifetime spend. A row-level trigger is perfect for updating the total after each individual purchase (INSERT).

-- A Row-Level AFTER INSERT trigger
CREATE TRIGGER update_user_total
    AFTER INSERT
    ON purchases
    FOR EACH ROW -- This is implied, but I'm being explicit
    EXECUTE FUNCTION update_user_total_function();

Now, imagine an admin runs a statement to apply a loyalty discount, UPDATE purchases SET amount = amount * 0.9 WHERE user_id = 123;. If this updates 50 rows, the row-level trigger fires 50 times, each one updating the user_totals row. That’s 50 separate updates to the same row! This is a great way to waste performance.

A smarter design might use a statement-level trigger after the bulk update to recalculate the total from scratch, once.

-- A Statement-Level AFTER UPDATE trigger
CREATE TRIGGER refresh_user_total
    AFTER UPDATE
    ON purchases
    FOR EACH STATEMENT
    EXECUTE FUNCTION refresh_user_totals_function();

The Function Association

Finally, you EXECUTE FUNCTION. Note that it’s FUNCTION, not PROCEDURE. The function must be defined to return TRIGGER (we’ll get into writing these functions next). This is a simple association: when the trigger fires, it calls this specific function.

The real art is in the combination. Choosing BEFORE UPDATE FOR EACH ROW gives you a function that can scrutinize and alter every new row version before it hits the table. Choosing AFTER DELETE FOR EACH STATEMENT gives you a function that can send a single notification email after a large purge operation. Think carefully about the combination of timing, event, and granularity. It defines everything about your trigger’s behavior.