Triggers vs Application Logic

Reviewed & published by Brayan K

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.

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.

What You'll Learn

• Write a trigger function and bind it with CREATE TRIGGER
• Use NEW and OLD to read the before/after row
• Pick the right timing: BEFORE, AFTER, INSTEAD OF
• Build an unbypassable audit-log trigger
• Keep a derived/denormalised column in sync
• Decide when logic belongs in the app, not the database

Real-World Analogy

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.

1. What a Trigger Is (and How It's Built)

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.

-- A trigger is a stored block of SQL the database runs
-- AUTOMATICALLY whenever a row is INSERTed, UPDATEd, or DELETEd.
-- You never call it — the database fires it for you.

-- In Postgres, a trigger has TWO parts:
--   1) a FUNCTION that holds the logic
--   2) a TRIGGER that binds that function to a table + event

CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER AS $$            -- $$ marks the function body
BEGIN
    NEW.updated_at = NOW();      -- NEW = the row as it will be saved
    RETURN NEW;                  -- BEFORE triggers must RETURN the row
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_set_updated_at
    BEFORE UPDATE ON products    -- WHEN: before an UPDATE on products
    FOR EACH ROW                 -- run once per affected row
    EXECUTE FUNCTION touch_updated_at();

-- Now every UPDATE to products silently refreshes updated_at.
-- The app never has to remember to set it.

2. Timing & Events — BEFORE, AFTER, INSTEAD OF

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.

-- The same idea on MySQL/MariaDB uses inline trigger bodies
-- (no separate function). Same concepts, different syntax:

CREATE TRIGGER trg_products_set_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    SET NEW.updated_at = NOW();   -- MySQL: logic lives in the trigger itself

-- TIMING + EVENT decide WHEN a trigger runs:
--   BEFORE INSERT/UPDATE  → can read AND change NEW before it is saved
--   AFTER  INSERT/UPDATE  → row is already saved; NEW/OLD are read-only
--   AFTER  DELETE         → only OLD exists (the row that was removed)
--   INSTEAD OF ...        → replaces the action (used on VIEWs only)

-- NEW = the incoming row; OLD = the row before the change.
-- INSERT has only NEW. DELETE has only OLD. UPDATE has both.

3. A Great Use: The Unbypassable Audit Log

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.

-- ✅ GREAT use of a trigger: an audit log you CANNOT bypass.
-- Even a buggy app or a hand-typed UPDATE gets recorded.

CREATE TABLE audit_log (
    id          SERIAL PRIMARY KEY,
    table_name  TEXT NOT NULL,
    operation   TEXT NOT NULL,          -- INSERT / UPDATE / DELETE
    old_data    JSONB,                  -- the row before (NULL for INSERT)
    new_data    JSONB,                  -- the row after  (NULL for DELETE)
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN          -- TG_OP tells you which event fired
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
        RETURN OLD;                   -- AFTER trigger: return value ignored
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSE  -- INSERT
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- One function, attached to as many tables as you like:
CREATE TRIGGER trg_products_audit
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW EXECUTE FUNCTION audit_changes();

-- Now: UPDATE products SET price = 27.99 WHERE id = 1;
--      DELETE FROM products WHERE id = 4;
-- ...both land in audit_log automatically (see the table below).

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:

4. A Great Use: Keeping a Derived Column in Sync

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.

-- ✅ GREAT use of a trigger: keep a DENORMALISED count in sync.
-- categories.product_count is a cached number we maintain on writes,
-- so reads never have to COUNT the products table.

CREATE OR REPLACE FUNCTION sync_product_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE categories SET product_count = product_count + 1
        WHERE id = NEW.category_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE categories SET product_count = product_count - 1
        WHERE id = OLD.category_id;
    ELSIF TG_OP = 'UPDATE' AND NEW.category_id IS DISTINCT FROM OLD.category_id THEN
        -- a product moved categories: -1 from the old, +1 to the new
        UPDATE categories SET product_count = product_count - 1 WHERE id = OLD.category_id;
        UPDATE categories SET product_count = product_count + 1 WHERE id = NEW.category_id;
    END IF;
    RETURN NULL;   -- AFTER ROW trigger: the return value is not used
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_count
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW EXECUTE FUNCTION sync_product_count();

-- Tip: for a PURELY computed value from the SAME row
-- (e.g. line_total = quantity * unit_price), prefer a
-- GENERATED column instead of a trigger — it's simpler and safer.

Your Turn: complete the audit trigger

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.

-- 🎯 YOUR TURN — finish this AFTER UPDATE audit trigger.
-- Goal: when a product's price changes, log the old and new price.

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    -- only log when the price actually changed
    IF NEW.price IS DISTINCT FROM OLD.price THEN
        INSERT INTO price_audit (product_id, old_price, new_price)
        VALUES (NEW.id, ___, ___);   -- 👉 the OLD price, then the NEW price
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_price_audit
    ___ UPDATE ON products           -- 👉 timing keyword: BEFORE or AFTER?
    FOR EACH ROW EXECUTE FUNCTION log_price_change();

-- ✅ Expected: blanks are  OLD.price ,  NEW.price , and the timing is AFTER.
--    (You only need OLD/NEW values, not to change the row → AFTER is correct.)

5. When to Push Logic to the Application Instead

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.

✅ Use a Trigger / DB feature for

• • Audit logs that must never be skipped
• • updated_at and other stamps
• • Denormalised counts / cached aggregates
• • Complex invariants CHECK can't express
• • Same-row math → a GENERATED column

❌ Use Application Code for

• • Sending emails / push notifications
• • Calling external / payment APIs
• • Multi-step business workflows
• • Anything slow, async, or non-deterministic
• • Logic that needs unit tests & code review

-- ❌ When NOT to use a trigger: hidden side-effects.
-- Triggers run INSIDE your transaction. Anything slow or external
-- (email, HTTP call, payment API) makes the whole write hang or
-- roll back in surprising ways.

-- ❌ BAD: emailing from a trigger
-- CREATE TRIGGER send_welcome_email AFTER INSERT ON users ...
--   If the mail server is down, the INSERT itself fails!

-- ✅ GOOD: do the write, then handle the side-effect in the app
--   BEGIN;
--     INSERT INTO users (email) VALUES ('[email protected]');
--   COMMIT;
--   queue_email(user.email, 'Welcome!');  -- async, AFTER the commit

-- ✅ GOOD: a same-row calculation belongs in a GENERATED column
ALTER TABLE order_items
    ADD COLUMN line_total NUMERIC(10,2)
    GENERATED ALWAYS AS (quantity * unit_price) STORED;

-- ✅ GOOD: a read-time calculation belongs in a VIEW (no hidden writes)
CREATE VIEW order_summary AS
SELECT o.order_id,
       SUM(oi.quantity * oi.unit_price)        AS subtotal,
       SUM(oi.quantity * oi.unit_price) * 0.20 AS vat
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.order_id
GROUP  BY o.order_id;

-- Rule of thumb: triggers for INVARIANTS the DB must guarantee;
-- app code for WORKFLOWS, anything external, and anything slow.

Your Turn: trigger or app logic?

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.

-- 🎯 YOUR TURN — trigger or application logic? Decide each scenario.
-- Replace each ___ with exactly one word:  TRIGGER  or  APP.

-- 1) Stamp updated_at on every row UPDATE ............ ___  -- 👉 simple, must always be consistent
-- 2) Send a "your order shipped" email .............. ___  -- 👉 slow + external service
-- 3) Keep an unbypassable audit_log of all deletes .. ___  -- 👉 must fire even on hand-typed SQL
-- 4) Charge a customer's card via a payment API ..... ___  -- 👉 external call inside a transaction = danger

-- ✅ Expected:  1) TRIGGER   2) APP   3) TRIGGER   4) APP
--    Pattern: invariants the DB must guarantee → TRIGGER.
--             anything external, slow, or async → APP.

6. The Architectural Pitfalls

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.

-- The four pitfalls that make people fear triggers.

-- PITFALL 1 — Cascading / recursive triggers.
-- A trigger on orders that UPDATEs orders fires itself again.
-- Fix: guard on what actually changed, IS DISTINCT FROM is your friend.
CREATE OR REPLACE FUNCTION on_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN   -- only when it really changed
        INSERT INTO order_history (order_id, status) VALUES (NEW.order_id, NEW.status);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- PITFALL 2 — Performance: row triggers fire ONCE PER ROW.
-- A 1,000,000-row UPDATE runs the trigger 1,000,000 times.
-- Fix: use a STATEMENT-level trigger for bulk work.
CREATE TRIGGER trg_logs_bulk
    AFTER INSERT ON logs
    FOR EACH STATEMENT          -- fires ONCE per statement, not per row
    EXECUTE FUNCTION summarise_batch();

-- PITFALL 3 — Ordering. When several triggers share a table/event,
-- Postgres fires them in ALPHABETICAL name order. Never rely on luck —
-- name them so the order is intentional: trg_10_validate, trg_20_audit.

-- PITFALL 4 — Hidden logic. A trigger is invisible in app code, so a
-- developer debugging "why did this row change?" may never think to look.
-- Fix: document every trigger and use a naming convention:
--   trg_{table}_{timing}_{event}   e.g. trg_orders_before_insert

Common Errors (and the fix)

• Hidden business logic in a trigger: a teammate spends hours asking "why did this row change?" because the cause is an invisible trigger. Keep business rules in tested app code; reserve triggers for stamps, audits, and invariants — and document every one.
• "stack depth limit exceeded" / infinite loop: a trigger on a table that updates the same table fires itself again. Guard with IF NEW.col IS DISTINCT FROM OLD.col THEN ... so it only acts when something relevant actually changed.
• Writes suddenly slow: a heavy FOR EACH ROW trigger runs once per row, so a bulk update multiplies the cost. Move expensive work to a FOR EACH STATEMENT trigger, or out to the app.
• Relying on trigger execution order: two triggers on the same event and you assume one runs first. Postgres orders them alphabetically by name — rename them (trg_10_validate, trg_20_audit) so the order is explicit, not accidental.
• "control reached end of trigger function without RETURN": a BEFORE row trigger must RETURN NEW (or RETURN OLD for delete). Returning NULL from a BEFORE trigger silently cancels the operation.

Trigger vs App — Decision Table

Frequently Asked Questions

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.

Q: Do NEW and OLD always both exist?

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.

Mini-Challenge: Soft-Delete Audit

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.

-- 🎯 MINI-CHALLENGE: a soft-delete audit trigger
-- Using ONLY what this lesson covered (CREATE FUNCTION, OLD/NEW, TG_OP,
-- a BEFORE/AFTER trigger, and a guard condition):
--
--   1. Write a function deleted_items_log() that, AFTER a DELETE on
--      products, inserts the removed row's id and name into a
--      deleted_items table (columns: product_id, product_name).
--   2. Use OLD (there is no NEW on a DELETE).
--   3. Attach it with an AFTER DELETE ... FOR EACH ROW trigger.
--
-- ✅ Expected: deleting product id=4 'Notebook' adds the row
--    (4, 'Notebook') to deleted_items, automatically.

-- your function + trigger here

🎉 Lesson Complete

• ✅ A trigger = a function bound to a table event, fired automatically by the database
• ✅ NEW/OLD expose the after/before row; BEFORE can change it, AFTER reacts to it
• ✅ Great uses: unbypassable audit logs, stamps, derived/denormalised columns, complex invariants
• ✅ Push side-effects (email, APIs, workflows) and anything slow into the application instead
• ✅ Defuse the traps: recursion guards, statement-level triggers, intentional naming/order, documentation
• ✅ Next: Schema Versioning — manage and ship database changes safely

Practice quiz

Continue this course

• Previous: Massive Data Handling: Bulk Inserts, Batching, ETL Patterns
• Next: Schema Versioning, Migration Tools & CI/CD for Databases — Version database schemas with Flyway, Liquibase, and CI/CD pipelines
• Quick reference: SQL cheat sheet

Related lessons

• INSERT/UPDATE/DELETE
• constraints
• transactions