PL/pgSQL BEFORE/AFTER INSERT Triggers

Master INSERT triggers — BEFORE for defaults and validation (modify NEW), AFTER for cascading inserts that need the auto-generated SERIAL ID. OLD is not available in INSERT triggers. Includes the racy summary-table pattern with explicit warning about concurrency. Critical fix: original function ended with $$; without LANGUAGE plpgsql clause.

INSERT triggers fire when a new row is added to a table. As with UPDATE triggers, the BEFORE/AFTER timing matters:

One thing INSERT triggers don't have access to: OLD. Nothing existed before, so there's no "old" row.

A common pattern: keep a denormalized total in a separate table that gets updated on every insert. Two tables — the main one and the summary — and a BEFORE INSERT trigger that keeps them in sync.

DROP TABLE IF EXISTS inventory_stat;
DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    quantity   INT NOT NULL DEFAULT 0
);

CREATE TABLE inventory_stat (
    total_qty INT
);

This function maintains a running total in inventory_stat. If the summary row exists, increment its total; otherwise, create the row:

CREATE OR REPLACE FUNCTION update_total_qty()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    p_row_count INT;
BEGIN
    SELECT COUNT(*) INTO p_row_count FROM inventory_stat;

    IF p_row_count > 0 THEN
        UPDATE inventory_stat
        SET    total_qty = total_qty + NEW.quantity;
    ELSE
        INSERT INTO inventory_stat(total_qty)
        VALUES (NEW.quantity);
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER inventory_before_insert
    BEFORE INSERT ON inventory
    FOR EACH ROW
    EXECUTE FUNCTION update_total_qty();

INSERT INTO inventory(product_id, quantity) VALUES (1, 100);
SELECT * FROM inventory_stat;

INSERT INTO inventory(product_id, quantity) VALUES (2, 200);
SELECT * FROM inventory_stat;

First insert created the summary row with 100. Second insert added 200, totaling 300. The trigger ran transparently inside each INSERT.

Another common use of BEFORE INSERT: filling in column values the application didn't supply. This trigger ensures every row has a creation timestamp, even if the application omitted it:

CREATE TABLE IF NOT EXISTS articles (
    article_id  SERIAL PRIMARY KEY,
    title       VARCHAR(200),
    body        TEXT,
    created_at  TIMESTAMP,
    created_by  VARCHAR(100)
);

CREATE OR REPLACE FUNCTION articles_set_defaults()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.created_at IS NULL THEN
        NEW.created_at := CURRENT_TIMESTAMP;
    END IF;

    IF NEW.created_by IS NULL THEN
        NEW.created_by := CURRENT_USER;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER articles_before_insert
    BEFORE INSERT ON articles
    FOR EACH ROW
    EXECUTE FUNCTION articles_set_defaults();

The most common AFTER INSERT pattern: when a row is inserted into one table, automatically insert a related row into another. The classic example: every new member gets a free-tier membership record by default.

DROP TABLE IF EXISTS memberships;
DROP TABLE IF EXISTS members;

CREATE TABLE members (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE memberships (
    id              SERIAL PRIMARY KEY,
    member_id       INT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    membership_type VARCHAR(50) NOT NULL DEFAULT 'free'
);

CREATE OR REPLACE FUNCTION create_default_membership()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO memberships (member_id)        -- membership_type defaults to 'free'
    VALUES (NEW.id);

    RETURN NEW;
END;
$$;

CREATE TRIGGER members_after_insert
    AFTER INSERT ON members
    FOR EACH ROW
    EXECUTE FUNCTION create_default_membership();

Why AFTER and not BEFORE? Because we need NEW.id — the auto-generated SERIAL primary key — to use as a foreign key. In a BEFORE INSERT trigger, NEW.id might still be NULL (the database hasn't assigned the sequence value yet, depending on the implementation). In an AFTER trigger, the value is guaranteed to be set.

INSERT INTO members (name, email)
VALUES ('John Doe', 'john.doe@example.com');

SELECT * FROM members;
SELECT * FROM memberships;

One INSERT into members, two rows in the database — the trigger handled the cascade. Adding a member without a membership becomes structurally impossible.

1. OLD doesn't exist for INSERT triggers. Don't reference it; use NEW for everything.
2. Use AFTER INSERT when you need the auto-generated ID. SERIAL primary keys may not be assigned yet in BEFORE INSERT.
3. Prefer column DEFAULTs to BEFORE INSERT triggers for simple defaults. DEFAULT CURRENT_TIMESTAMP beats a trigger.
4. Watch for race conditions in summary-table maintenance. Concurrent INSERTs can produce wrong totals. Use FOR UPDATE locks or atomic ON CONFLICT patterns.
5. Always specify LANGUAGE plpgsql in the function definition. Without it, the function body won't parse.
6. Use RETURNING at the call site when you need the new row's auto-generated values for further processing — cleaner than relying on currval() or trigger side effects.

• BEFORE INSERT triggers fire before the row is written. They can validate, modify NEW, fill in defaults, or RETURN NULL to skip.
• AFTER INSERT triggers fire after the row is written. They see the final row including auto-generated IDs and can cascade to related tables.
• OLD is not available in INSERT triggers — only NEW.
• Use BEFORE for shaping the row, AFTER for reacting to its creation.
• Summary-table patterns (running totals updated by triggers) are racy under concurrency — be aware of the trade-offs.
• Auto-cascading inserts (one INSERT triggering another) are a clean way to enforce "this row implies that row" rules at the database level.