Oracle Triggers in PostgreSQL: Full Migration Guide

Same Idea, Different Shape

Triggers are one of the friendliest parts of an Oracle → PostgreSQL migration: the concepts map almost one-to-one, and most of the work is mechanical. But there is one structural difference that trips up every first-timer. Oracle inlines the trigger body inside the CREATE TRIGGER statement. PostgreSQL splits it in two: a trigger function that returns a special trigger type, and a CREATE TRIGGER that binds that function to a table and an event.

Once you internalise that split, ninety-plus percent of triggers convert by rote. This guide covers the rote part, the three gotchas that actually bite (the RETURN rule, firing order, and autonomous transactions), and the one place Oracle's pain simply disappears.

The Translation Map

A Worked Example

Here is a typical Oracle BEFORE INSERT row trigger: it stamps an audit column and normalises an email to lowercase before the row is written.

-- Oracle: body lives inside the trigger
CREATE OR REPLACE TRIGGER hr.emp_biu
BEFORE INSERT OR UPDATE ON hr.employees
FOR EACH ROW
BEGIN
  :NEW.email      := LOWER(:NEW.email);
  :NEW.updated_at := SYSDATE;
  IF INSERTING THEN
    :NEW.created_at := SYSDATE;
  END IF;
END;

In PostgreSQL the same logic becomes a function plus a binding. Note the three changes: drop the colons, branch on TG_OP instead of INSERTING, and — the one that catches people — RETURN NEW. In a BEFORE row trigger, the row that actually gets written is whatever you return; forget it and your edits (and the row) vanish.

-- PostgreSQL: function...
CREATE OR REPLACE FUNCTION hr.emp_biu() RETURNS trigger AS $$
BEGIN
  NEW.email      := LOWER(NEW.email);
  NEW.updated_at := NOW();
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := NOW();
  END IF;
  RETURN NEW;          -- required, or the row/changes are dropped
END;
$$ LANGUAGE plpgsql;

-- ...and binding
CREATE TRIGGER emp_biu
BEFORE INSERT OR UPDATE ON hr.employees
FOR EACH ROW EXECUTE FUNCTION hr.emp_biu();

The RETURN Rule (Read This Twice)

• BEFORE row trigger: RETURN NEW to proceed with (possibly modified) row; RETURN OLD on DELETE; RETURN NULL to silently skip the operation for that row.
• AFTER row trigger: the return value is ignored, but you still need a RETURN statement — conventionally RETURN NULL.
• Statement-level trigger: RETURN NULL; there is no row in scope.

Firing Order & Compound Triggers

Oracle lets you sequence triggers with FOLLOWS and bundle related timing points into a single compound trigger. PostgreSQL has neither — but it has a simple, deterministic rule: triggers fire in alphabetical order by name for a given event and timing. Split an Oracle compound trigger into separate PG triggers and encode the order in the names (emp_10_audit, emp_20_normalise). It reads explicitly and never surprises you.

Where Oracle's Pain Disappears

One genuine upgrade: PostgreSQL has no mutating-table error. Oracle's infamous ORA-04091 — a row trigger that queries or modifies its own table — forces awkward workarounds (compound triggers, package state, autonomous transactions). In PG, a trigger can read and write its own table freely. A whole category of Oracle trigger gymnastics simply evaporates; when you see a compound trigger built only to dodge ORA-04091, the PostgreSQL version is usually a single, plain trigger.

The Hard 5%: Autonomous Transactions

The construct that needs a human is the audit trigger that commits independently of the parent transaction — Oracle's PRAGMA AUTONOMOUS_TRANSACTION, used so the audit row survives even if the main transaction rolls back. PostgreSQL has no native autonomous transaction. The honest options: write the audit row through dblink (a loopback connection that commits on its own), use the pg_background extension, or — often the better answer — reconsider whether the audit really needs to survive a rollback. This is the part to flag in assessment and budget review time for, not to find on cutover night.

How DBMigrateAIPro Handles Triggers

The transpiler does the mechanical work: it lifts each Oracle trigger body into a PL/pgSQL function, generates the matching CREATE TRIGGER binding, rewrites :NEW/:OLD, swaps SYSDATE→NOW() and the rest of the built-ins, inserts the correct RETURN, and converts INSERTING/UPDATING/DELETING to TG_OP checks. Compound triggers are split into name-ordered triggers automatically. What it will not do silently is paper over an autonomous transaction — those are flagged in the gap report as a hard construct that needs a decision, with the line located, so the 5% that needs judgement is named up front rather than discovered later.