PL/pgSQL CREATE TRIGGER

Master CREATE TRIGGER — the two-step model of defining a trigger function then binding it to a table, the EXECUTE FUNCTION clause, conditional firing with WHEN, multi-event triggers via OR, and the TG_OP dispatch pattern. Critical fixes: original tutorial had stray return_type in syntax, included LANGUAGE clause inside CREATE TRIGGER (it belongs in CREATE FUNCTION), and referenced NEW.NAME for non-existent column.

Creating a trigger in PostgreSQL is a two-step process:

1. Define a trigger function — a regular PL/pgSQL function with no parameters and return type trigger.
2. Bind that function to a table via the CREATE TRIGGER statement, specifying when (BEFORE/AFTER) and on what (INSERT/UPDATE/etc.) it should fire.

The two-step model is intentional. One trigger function can be reused across multiple triggers — the same audit-logging function, for example, might be wired up to several tables.

Three things to know about the function:

• No parameters. The trigger system passes data via implicit variables (NEW, OLD, TG_OP, etc.), not function arguments.
• Returns type TRIGGER. Specifically, the function should RETURN a row record (or NULL).
• Different return semantics for different timings. See the table below.

The classic use case: log every insertion to a users table into an audit table. Walk through it step by step.

DROP TABLE IF EXISTS user_audit;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    user_id    SERIAL PRIMARY KEY,
    username   VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT current_timestamp
);

CREATE TABLE user_audit (
    audit_id    SERIAL PRIMARY KEY,
    user_id     INTEGER,
    username    VARCHAR(50),
    action      VARCHAR(10),
    action_time TIMESTAMP DEFAULT current_timestamp
);

This function will run after every insertion, copying the new user data into the audit table:

CREATE OR REPLACE FUNCTION log_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_audit (user_id, username, action)
    VALUES (NEW.user_id, NEW.username, TG_OP);

    RETURN NEW;
END;
$$;

Three things worth noting:

• NEW.user_id and NEW.username reference the row that was just inserted into users.
• TG_OP is the operation name as a string ('INSERT') — using it instead of a hardcoded literal lets the same function handle multiple operations.
• RETURN NEW — for an AFTER trigger this return value is ignored, but it's still required syntactically. Convention is to return NEW.

CREATE TRIGGER user_insert_trigger
    AFTER INSERT
    ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_new_user();

INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

SELECT * FROM user_audit;

The trigger fired automatically for each insert — no application code involved. The audit table now has its log without anyone explicitly writing to it.

The optional WHEN clause lets the trigger fire only when a condition holds — useful when you want a single function applied selectively:

CREATE TRIGGER audit_salary_changes
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN (NEW.salary IS DISTINCT FROM OLD.salary)
    EXECUTE FUNCTION log_salary_change();

The trigger fires only when the salary actually changed — not when other columns were updated. The IS DISTINCT FROM operator handles NULL comparisons correctly (unlike !=, which returns NULL if either side is NULL).

One trigger can handle several event types via OR:

CREATE TRIGGER user_change_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_change();

Inside the trigger function, branch on TG_OP to decide what to do for each event type:

CREATE OR REPLACE FUNCTION log_user_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO user_audit (user_id, username, action)
        VALUES (NEW.user_id, NEW.username, 'INSERT');
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO user_audit (user_id, username, action)
        VALUES (NEW.user_id, NEW.username, 'UPDATE');
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO user_audit (user_id, username, action)
        VALUES (OLD.user_id, OLD.username, 'DELETE');
        RETURN OLD;
    END IF;

    RETURN NULL;  -- defensive — should never reach here
END;
$$;

Triggers are removed with DROP TRIGGER:

DROP TRIGGER IF EXISTS user_insert_trigger ON users;

1. Name triggers descriptively. A trigger called trg1 tells you nothing; users_after_insert_audit tells you when it fires and what it does.
2. Use EXECUTE FUNCTION in new code, not EXECUTE PROCEDURE. Both work, but the modern name reflects that triggers really do call functions, not procedures.
3. Don't put LANGUAGE in CREATE TRIGGER. The language clause belongs in CREATE FUNCTION; the trigger just wires the function to a table.
4. Use WHEN conditions to filter cheaply. Filtering inside the function still incurs the function-call overhead; WHEN is checked first, before the function runs.
5. Branch on TG_OP when one function handles multiple operations. Saves duplication; keeps logic in one place.
6. Always include DROP TRIGGER IF EXISTS in deployment scripts. CREATE OR REPLACE TRIGGER doesn't exist in PostgreSQL the way it does for functions.

• Creating a trigger is a two-step process: define a trigger function (returning trigger) then bind it to a table with CREATE TRIGGER.
• The trigger function takes no parameters; it accesses row data via implicit NEW and OLD records, plus TG_OP, TG_NAME, and other TG_* variables.
• CREATE TRIGGER specifies the timing (BEFORE/AFTER/INSTEAD OF), event(s), table, scope (ROW/STATEMENT), and an optional WHEN condition.
• Use EXECUTE FUNCTION (modern) over EXECUTE PROCEDURE (legacy synonym).
• LANGUAGE belongs in CREATE FUNCTION, not CREATE TRIGGER.
• One trigger function can be reused across multiple triggers — branch on TG_OP when handling multiple events.
• Drop a trigger with DROP TRIGGER trigger_name ON table_name — trigger names are scoped to their table.