Triggers

What we're doing: Show a BEFORE trigger silently rejecting an invalid row (by returning NULL) versus an AFTER trigger, which by the time it runs can only observe, not stop, the same write.

CREATE FUNCTION reject_negative_price() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.price < 0 THEN
        RETURN NULL;  -- BEFORE only: silently skips this row's insert
    END IF;
    RETURN NEW;
END;
$$;
CREATE TRIGGER guard_price BEFORE INSERT ON products
    FOR EACH ROW EXECUTE FUNCTION reject_negative_price();

INSERT INTO products (name, price) VALUES ('Widget', -5);
SELECT count(*) FROM products WHERE name = 'Widget';

3

The row is inspected before it is ever written — this is only possible in a BEFORE trigger.

4

Returning NULL from a row-level BEFORE trigger tells the executor to skip this row entirely — no error, no row.

11

The row genuinely never existed; an AFTER trigger could never have prevented this, since it fires only once the write already succeeded.

INSERT 0 0

 count
-------
     0

Why this works: A BEFORE trigger sits in the one place in the pipeline where "should this row exist at all" can still be decided — by the time an AFTER trigger runs, that decision has already been made and committed within the transaction, which is exactly why AFTER triggers are reserved for reacting to facts rather than vetoing them.

What we're doing: Compare a row-level trigger and a statement-level trigger on the same bulk DELETE, counting how many times each actually fires.

CREATE TABLE fire_log (kind text);

CREATE FUNCTION log_row() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN INSERT INTO fire_log VALUES ('row'); RETURN OLD; END; $$;
CREATE TRIGGER t_row AFTER DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION log_row();

CREATE FUNCTION log_stmt() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN INSERT INTO fire_log VALUES ('statement'); RETURN NULL; END; $$;
CREATE TRIGGER t_stmt AFTER DELETE ON orders
    FOR EACH STATEMENT EXECUTE FUNCTION log_stmt();

DELETE FROM orders WHERE status = 'cancelled';  -- deletes 4 rows
SELECT kind, count(*) FROM fire_log GROUP BY kind;

3–6

The row-level trigger logs once for every row DELETE actually removes.

8–11

The statement-level trigger logs once, no matter how many rows the same DELETE removed.

   kind    | count
-----------+-------
 row       |     4
 statement |     1

Why this works: The single DELETE statement removed 4 rows, and each trigger honored its own granularity exactly as declared — the row-level trigger cannot "know" about the other rows in the same statement, while the statement-level trigger never had access to any individual row in the first place, which is precisely why it is far cheaper for bulk operations that do not need per-row logic.

What we're doing: Write a trigger that keeps a denormalized order_items_count column on orders in sync whenever order_items rows are inserted or deleted — a genuine cross-table invariant a CHECK constraint cannot express.

CREATE FUNCTION sync_item_count() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE orders SET item_count = item_count + 1 WHERE id = NEW.order_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE orders SET item_count = item_count - 1 WHERE id = OLD.order_id;
    END IF;
    RETURN NULL;  -- AFTER trigger, return value is ignored
END;
$$;

CREATE TRIGGER keep_item_count_in_sync
    AFTER INSERT OR DELETE ON order_items
    FOR EACH ROW EXECUTE FUNCTION sync_item_count();

3

TG_OP tells the trigger function which operation fired it — a standard PL/pgSQL trigger variable.

12–13

The trigger lives on order_items but keeps a column on the separate orders table correct — exactly the cross-table invariant a CHECK constraint structurally cannot express.

CREATE FUNCTION
CREATE TRIGGER

Why this works: A CHECK constraint can only look at the row currently being written, never at other rows or other tables, so a "denormalized count on a different table" is squarely a job that requires a trigger — this is the honest case where triggers exist to do something no simpler mechanism can.

What we're doing: Show two same-timing triggers on one table firing in alphabetical order regardless of creation order, then show a genuinely recursive trigger with a guard condition to stop it.

-- created in this order: "zzz_last" first, "aaa_first" second
CREATE TRIGGER zzz_last BEFORE INSERT ON logs
    FOR EACH ROW EXECUTE FUNCTION log_step('zzz_last');
CREATE TRIGGER aaa_first BEFORE INSERT ON logs
    FOR EACH ROW EXECUTE FUNCTION log_step('aaa_first');
-- fires aaa_first, THEN zzz_last -- alphabetical by name, ignoring creation order

CREATE FUNCTION split_large_order() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.qty > 100 AND NOT NEW.already_split THEN
        INSERT INTO orders (qty, already_split) VALUES (NEW.qty - 100, true);
        NEW.qty := 100;
        NEW.already_split := true;  -- the guard: prevents infinite recursion
    END IF;
    RETURN NEW;
END;
$$;
CREATE TRIGGER auto_split BEFORE INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION split_large_order();

2–5

"zzz_last" was created first, but its name sorts after "aaa_first" — it fires second regardless.

12

The guard condition (already_split) is what stops this trigger from firing itself indefinitely on the row it just inserted.

-- (no direct output -- the point is fire order and the recursion guard, not a query result)

Why this works: The alphabetical-ordering rule is a real, documented PostgreSQL behavior that silently overrides whatever order a migration file created triggers in — without a deliberate naming convention, two triggers whose relative order matters can produce a subtly wrong result with no error anywhere. The recursion guard on split_large_order() is required precisely because PostgreSQL enforces no depth limit on its own — an unguarded version of this trigger would recurse until it errored out or exhausted resources.

What we're doing: Show how to make an implicit trigger-driven workflow explicit again — using \d+ to surface what a single INSERT actually triggers, as a concrete debugging/documentation step.

\d+ orders

-- Triggers:
--   deduct_inventory AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION deduct_inventory_fn()
--   audit_order_insert AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION write_audit_log()
--   check_low_stock AFTER UPDATE ON inventory FOR EACH ROW EXECUTE FUNCTION check_low_stock_fn()
--   (the last one only becomes relevant because deduct_inventory_fn() updates the inventory table)

-- to actually see it run, query pg_stat_user_functions before/after,
-- or add RAISE NOTICE statements temporarily inside each trigger function

1

\d+ is the single most direct way to make a table's hidden trigger behavior visible again before touching it.

3–6

None of these three triggers would be found by grepping the application's codebase for "orders" — they only exist in the schema.

(psql \d+ output listing every trigger attached to the table)

Why this works: Making the implicit explicit — by habitually checking \d+ before modifying a table with triggers, or documenting the trigger-driven workflow directly alongside the schema — is the practical mitigation for the fact that trigger call graphs do not show up anywhere application tooling looks by default.