Published: 2026-06-14

Triggers are the hardest SQL Server object to move to PostgreSQL, because the model is genuinely different: T-SQL triggers are statement-level and see sets of rows; PostgreSQL triggers are usually row-level and see one row, through a separate function. Here's how to port them without changing what they do.

As with every procedural object, Jam SQL Studio won't auto-translate a trigger body for you — T-SQL and PL/pgSQL are too different for a safe mechanical rewrite. What it does is move the tables and data, then list every trigger that exists on the SQL Server side and not yet on PostgreSQL, so you know exactly what's left. This post is the rewrite itself.

The two structural differences

1. Logic lives in a separate function. SQL Server inlines the body in CREATE TRIGGER. PostgreSQL splits it: a FUNCTION ... RETURNS TRIGGER holds the body, and a CREATE TRIGGER binds that function to a table and events. One T-SQL trigger becomes two PostgreSQL objects.
2. Row-level vs statement-level. A T-SQL trigger fires once per statement and exposes every affected row through the INSERTED and DELETED pseudo-tables. A PostgreSQL FOR EACH ROW trigger fires once per row and exposes that single row as NEW (the new values) and OLD (the prior values). Mapping INSERTED→NEW and DELETED→OLD only works when the logic was already per-row.

The common case: a per-row audit trigger

An AFTER UPDATE trigger that stamps a modified time and writes an audit row is the most common shape, and it ports cleanly because the logic is naturally per-row.

-- T-SQL (SQL Server)
CREATE TRIGGER trg_Products_Audit
ON dbo.Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (ProductId, OldPrice, NewPrice, ChangedAt)
    SELECT i.Id, d.Price, i.Price, GETDATE()
    FROM inserted i
    JOIN deleted d ON i.Id = d.Id
    WHERE i.Price <> d.Price;
END;

In PostgreSQL this becomes a function plus a trigger:

-- PL/pgSQL (PostgreSQL)
CREATE FUNCTION products_audit() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO product_audit (product_id, old_price, new_price, changed_at)
        VALUES (NEW.id, OLD.price, NEW.price, now());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_audit
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION products_audit();

The mapping is direct: inserted i → NEW, deleted d → OLD, the JOIN ... ON i.Id = d.Id disappears (NEW and OLD are already the same row), and the WHERE i.Price <> d.Price becomes an IF. An AFTER row trigger should RETURN NEW (the return value is ignored for AFTER, but it's the convention); a BEFORE trigger returns NEW to proceed or NULL to skip the row.

The hard case: set-based logic

The per-row port above silently changes behaviour if the original trigger was doing set work — for example, updating a summary table once with an aggregate over all affected rows. Run that body once per row and you get N updates instead of one, which is both slower and potentially wrong.

PostgreSQL's faithful answer is a statement-level trigger with transition tables (version 10+). You name the row sets and get back something that looks and behaves like INSERTED/DELETED:

CREATE FUNCTION products_rollup() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO product_audit (product_id, old_price, new_price, changed_at)
    SELECT i.id, d.price, i.price, now()
    FROM inserted i
    JOIN deleted d ON i.id = d.id
    WHERE i.price <> d.price;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_products_rollup
AFTER UPDATE ON products
REFERENCING NEW TABLE AS inserted OLD TABLE AS deleted
FOR EACH STATEMENT
EXECUTE FUNCTION products_rollup();

Now the body is almost identical to the original T-SQL — same inserted/deleted join, evaluated once for the whole statement. This is the conversion to reach for whenever the T-SQL trigger's logic touched INSERTED/DELETED as a set. A statement-level trigger returns NULL.

The mapping at a glance

When one T-SQL trigger handles several events, PostgreSQL lets you keep that in a single function and branch on the special TG_OP variable ('INSERT', 'UPDATE', 'DELETE') — often cleaner than the T-SQL original, which has to inspect INSERTED/DELETED emptiness to tell the operation apart.

Where Jam SQL Studio helps

• It finds the triggers for you. After you migrate the tables, a cross-engine Schema Compare reports every trigger present on SQL Server and missing on PostgreSQL — triggers are easy to forget because they're attached to tables rather than listed on their own.
• It keeps the original in view. Script the trigger as CREATE to read the exact T-SQL, then write the PostgreSQL function and trigger in a query editor beside it.
• It never guesses. Because Jam emits a -- [MANUAL PORT] note rather than a machine-translated trigger body, you're never debugging a subtly-wrong auto-conversion — the rewrite is honest work you can trust.

Triggers reward careful reading over clever tooling. Use the T-SQL vs PL/pgSQL reference for the surrounding syntax, decide row-level vs statement-level from intent, and let Jam handle the tables underneath.

Frequently asked questions

Download Jam SQL Studio — free for personal use on macOS, Windows, and Linux.