Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 2 · Lesson 10 · 6 min read

Converting SQL Server Triggers to PostgreSQL

On this page
  1. Migrating a SQL Server audit trigger
  2. Structure: a function, then a trigger
  3. Option 1: a row-level trigger
  4. Option 2: a statement-level trigger with transition tables
  5. Timing: BEFORE, AFTER and INSTEAD OF
  6. Check yourself

Migrating a SQL Server audit trigger

Most SQL Server databases have at least one trigger like this, logging every change to an important table:

SQL
-- T-SQL
CREATE TRIGGER trg_Accounts_Audit ON dbo.Accounts
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.AccountsAudit (AccountId, Operation, OldBalance, NewBalance, ChangedBy, ChangedAt)
    SELECT COALESCE(i.AccountId, d.AccountId),
           CASE WHEN i.AccountId IS NOT NULL AND d.AccountId IS NOT NULL THEN 'UPDATE'
                WHEN i.AccountId IS NOT NULL THEN 'INSERT' ELSE 'DELETE' END,
           d.Balance, i.Balance, SUSER_SNAME(), SYSUTCDATETIME()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.AccountId = d.AccountId;
END;

PostgreSQL gives you two ways to migrate it. Knowing both helps, because the right choice depends on how much data each statement changes.

Structure: a function, then a trigger

In SQL Server a trigger is one object with its logic inside. In PostgreSQL it's always two:

  1. A trigger function, created with CREATE FUNCTION ... RETURNS trigger, holding the logic.
  2. A trigger, created with CREATE TRIGGER, saying which table, which events, when, and which function to run.

One function can serve triggers on many tables. That's handy for generic audit logging.

Option 1: a row-level trigger

SQL Server triggers fire once per statement. PostgreSQL lets you choose, and FOR EACH ROW runs the function once per changed row. Inside it you get:

  • NEW: the row after the change (INSERT and UPDATE).
  • OLD: the row before the change (UPDATE and DELETE).
  • TG_OP: the operation, as 'INSERT', 'UPDATE' or 'DELETE'.
SQL
CREATE TABLE accounts (account_id int PRIMARY KEY, balance numeric(12,2) NOT NULL);
CREATE TABLE accounts_audit (
  audit_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id   int,
  operation    text,
  old_balance  numeric(12,2),
  new_balance  numeric(12,2),
  changed_by   text NOT NULL DEFAULT current_user,
  changed_at   timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION audit_accounts_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO accounts_audit (account_id, operation, old_balance)
    VALUES (OLD.account_id, TG_OP, OLD.balance);
  ELSE
    INSERT INTO accounts_audit (account_id, operation, old_balance, new_balance)
    VALUES (NEW.account_id, TG_OP,
            CASE WHEN TG_OP = 'UPDATE' THEN OLD.balance END, NEW.balance);
  END IF;
  RETURN NULL;   -- the return value is ignored for AFTER triggers
END;
$$;

CREATE TRIGGER accounts_audit_row
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_accounts_row();

TG_OP replaces the CASE expression that worked out the operation from inserted and deleted. SUSER_SNAME() becomes current_user, and SYSUTCDATETIME() becomes the now() default on a timestamptz column.

Be careful with one default. Without FOR EACH ROW, CREATE TRIGGER creates a statement-level trigger, and NEW and OLD are NULL inside the function.

Option 2: a statement-level trigger with transition tables

A row-level trigger on a 2-million-row UPDATE runs 2 million times. For bulk changes, a statement-level trigger is closer to how SQL Server works: it runs once and sees all the changed rows as tables. PostgreSQL calls them transition tables, and you can even name them inserted and deleted:

SQL
CREATE FUNCTION audit_accounts_update() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO accounts_audit (account_id, operation, old_balance, new_balance)
  SELECT d.account_id, 'UPDATE', d.balance, i.balance
  FROM deleted d JOIN inserted i USING (account_id);
  RETURN NULL;
END;
$$;

DROP TRIGGER accounts_audit_row ON accounts;

CREATE TRIGGER accounts_audit_update
AFTER UPDATE ON accounts
REFERENCING OLD TABLE AS deleted NEW TABLE AS inserted
FOR EACH STATEMENT EXECUTE FUNCTION audit_accounts_update();

INSERT INTO accounts VALUES (1, 10), (2, 20);
UPDATE accounts SET balance = balance + 1;
SELECT account_id, operation, old_balance, new_balance FROM accounts_audit;
-- 1 | UPDATE | 10.00 | 11.00
-- 2 | UPDATE | 20.00 | 21.00

There's one catch the SQL Server version doesn't have. A trigger with transition tables can only cover one event. AFTER INSERT OR UPDATE ... REFERENCING fails with "transition tables cannot be specified for triggers with more than one event". So you create one trigger per event: an INSERT trigger referencing NEW TABLE, an UPDATE trigger referencing both, and a DELETE trigger referencing OLD TABLE. They can call three small functions, or one function that checks TG_OP.

Which option to choose: row-level triggers are simpler to read and fine for tables changed a few rows at a time. Statement-level triggers with transition tables are much cheaper for bulk updates, and they're the closest translation of set-based T-SQL trigger logic.

Timing: BEFORE, AFTER and INSTEAD OF

TimingSQL ServerPostgreSQL
AFTERYesYes, on tables
BEFORENoYes, row-level BEFORE triggers can change NEW or skip the row
INSTEAD OFOn tables and viewsOn views only

The table holds two migration surprises. First, PostgreSQL has BEFORE triggers, which SQL Server lacks. Logic you implemented in SQL Server as an INSTEAD OF trigger on a table, to adjust values before saving, usually becomes a simpler BEFORE trigger that modifies NEW and returns it. Returning NULL from a BEFORE row trigger silently skips that row.

Second, PostgreSQL doesn't allow INSTEAD OF triggers on tables. Rewrite those as BEFORE triggers, or move the logic into a function the application calls.

A few smaller differences:

  • Firing order. SQL Server needs sp_settriggerorder. PostgreSQL fires triggers for the same event in alphabetical order of name.
  • Disabling. DISABLE TRIGGER t ON tbl becomes ALTER TABLE tbl DISABLE TRIGGER t.
  • Detecting changes. NEW.balance IS DISTINCT FROM OLD.balance compares values safely when NULLs are involved. SQL Server 2022 added the same operator, but older T-SQL usually used ISNULL tricks.

In DataGrip, triggers appear under each table's Triggers node, and the trigger functions under Routines.

Check yourself

The trigger was created without FOR EACH ROW, so it defaults to a statement-level trigger, which runs once per statement and doesn't set NEW or OLD. Recreate it with FOR EACH ROW, or keep it statement-level and read the changed rows from transition tables.

Replace the row-level trigger with a statement-level AFTER UPDATE trigger using transition tables, so the audit rows are written with one set-based INSERT per statement.

Use a BEFORE INSERT row trigger whose function sets NEW.code := upper(NEW.code) and returns NEW. PostgreSQL doesn't support INSTEAD OF triggers on tables, and BEFORE triggers exist for exactly this.

In a row-level trigger, NEW and OLD hold the single row being changed. In a statement-level trigger, REFERENCING NEW TABLE AS inserted OLD TABLE AS deleted gives you transition tables that work much like SQL Server's, which makes set-based audit triggers easy to migrate.

The value returned is the row that will actually be written. Returning NEW, possibly modified, saves the row; returning NULL silently skips the operation for that row. Forgetting the return is the classic cause of inserts that vanish without an error.