Right, let’s talk about making your triggers fast, or at least, not catastrophically slow. This isn’t optional. A badly written trigger isn’t just a minor performance hiccup; it’s a landmine waiting for the wrong query to step on it. Your database will feel like it’s running in molasses, and you’ll be left staring at pg_stat_activity wondering which of your life choices led you here. I’ve been there. Let’s not do that.
Right, so you’ve mastered row-level triggers and you’re feeling pretty good. You can make your database dance a little jig every time a single row is updated. Cute. But what if you want to conduct the entire orchestra? What if you need to catch an event as broad as “someone just dropped a table” or “a new function was created”? You don’t care about the individual rows; you care about the entire statement and its metaphysical impact on your database’s schema. For that, you need the big guns: Event Triggers.
Right, so you want to use triggers. Excellent choice, or perhaps a necessary evil. Either way, you’re here because you need the database to do something automatically, something integral enough to your data’s integrity that you can’t trust the application layer to always get it right. Let’s talk about the three most common jobs we give these digital automatons: keeping a secret history, stamping time on everything like an overzealous bureaucrat, and maintaining derived data so you don’t have to.
Right, so you’ve built a trigger. It fires every single time its triggering event happens. That’s its job. But what if you only want it to sometimes do its job? Do you really want to write a trigger that fires on every UPDATE to a million-row table, only for its first line to be IF (new.is_interesting IS FALSE) THEN RETURN NULL; END IF;? That’s like accepting a delivery job where you have to drive to every house in the city just to read the instructions on the box to see if it’s actually for you. It’s wasteful.
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:
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.
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.