Triggers Architecture

By the end of this lesson you'll be able to write database triggers with CREATE TRIGGER , use NEW and OLD to capture row changes, choose the right BEFORE / AFTER / INSTEAD OF timing — and, just as importantly, recognise the architectural traps that mean some logic belongs in your application instead. Triggers are powerful and invisible, which makes knowing when not to use them a senior-level skill.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

A trigger is like a security camera wired into a doorway . You don't press a button — the moment anyone passes through (a row changes), it records automatically and nobody can sneak past it. That's perfect for an audit trail. But you wouldn't wire that camera to also phone the police, email the owner, and reboil the kettle — pile too much onto an automatic reflex and a slow phone line freezes the whole doorway. Cameras (triggers) are for guaranteed, instant, in-house reactions; phoning the outside world (emails, APIs, workflows) belongs to a person on the other side of the door: your application.

A trigger is a block of SQL the database runs automatically whenever a row is inserted, updated, or deleted on a table. You never call it yourself — that's the whole point. In PostgreSQL a trigger comes in two pieces: a trigger function that holds the logic, and the CREATE TRIGGER statement that binds that function to a table and an event.

Inside the function you get two special row variables: NEW (the row as it will be saved) and OLD (the row as it was before the change). An INSERT has only NEW ; a DELETE has only OLD ; an UPDATE has both.

The timing decides when the trigger runs relative to the change. BEFORE runs first and can still modify NEW before it is written — ideal for normalising or stamping values. AFTER runs once the row is already saved, so NEW / OLD are read-only — ideal for logging or syncing other tables. INSTEAD OF replaces the action entirely and is used on views to make them writable.

Quick rule: if you need to change the row being saved , use BEFORE . If you just need to react to a change that already happened , use AFTER .

This is the strongest argument for triggers. Because the database fires them itself, an audit trigger records every change — including ones from a buggy code path, a forgotten admin script, or a query typed by hand at 2am. Application-level logging can be skipped; a trigger cannot. Here TG_OP tells the function which event fired and TG_TABLE_NAME which table, so one function audits many tables.

After running UPDATE products SET price = 27.99 WHERE id = 1; then DELETE FROM products WHERE id = 4; , the audit_log table fills itself in — no app code involved:

Sometimes you store a denormalised value — a number deliberately duplicated so reads stay fast, like a product_count on each category. A trigger can keep that cached value correct on every insert, update, and delete, so it never drifts from reality.

Finish this AFTER UPDATE trigger that logs price changes using OLD and NEW . Fill the three blanks; the expected answers are in the comments so you can check yourself.

Triggers run inside the transaction that caused the change. That guarantee is exactly why audit logs work — and exactly why side-effects are dangerous. If a trigger emails a customer or calls a payment API and that external service is slow or down, your simple INSERT hangs or rolls back. The right move is to commit the database change, then perform the side-effect in your app, often on a background queue.

For each scenario, decide whether the logic belongs in a trigger or in application code . Replace each ___ with one word. The answers are in the comments.

Triggers earn their bad reputation from four failure modes. None are reasons to avoid triggers entirely — they're reasons to use them deliberately : guard against recursion, watch row-by-row performance, name triggers so their execution order is intentional (Postgres fires same-event triggers in alphabetical name order), and document them so they aren't invisible to the next developer.

Q: What's the difference between BEFORE and AFTER ?

BEFORE runs before the row is written and can still change NEW (e.g. stamp a timestamp). AFTER runs once the row is committed to the table, so NEW / OLD are read-only — use it to log or to update other tables.

No. INSERT has only NEW , DELETE has only OLD , and UPDATE has both. Touching OLD in an insert trigger (or NEW in a delete trigger) is an error.

Q: How do I stop a trigger calling itself forever?

Guard the work with IF NEW.col IS DISTINCT FROM OLD.col THEN ... so it only acts when the column you care about actually changed, and avoid updating the same table the trigger is attached to.

Q: If triggers are unbypassable, why not put all logic in them?

Because they're invisible to app developers, hard to unit-test, run inside the transaction (so anything slow or external blocks writes), and their relative order can surprise you. Use them for guarantees the database must enforce; keep workflows and side-effects in well-tested app code.

Put it all together — a brief, a blank canvas, and the expected behaviour in the comments. Write the function and the trigger, then run it on PostgreSQL to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary