Postgres will reject some trigger WHEN clauses, but only when the migration runs.

A few rules that catch people out:

  1. A statement-level trigger can't read OLD or NEW in its WHEN. There's no single row to compare.
  2. An INSERT trigger can't read OLD. There is no old row.
  3. A DELETE trigger can't read NEW. There is no new row.
  4. An INSTEAD OF trigger on a view can't have a WHEN at all.
  5. A WHEN can't contain a subquery.

If you write triggers by hand in a custom migration, you find these out at deploy time.

In drizzle-pg-extras, my open-source package for declaring Postgres triggers, functions, comments and extensions next to a Drizzle schema, pgTrigger checks these when you declare the trigger, so generate fails before a bad migration is written:

export const touchUsers = pgTrigger("users_set_updated_at", { table: users, timing: "before", events: ["update"], columns: [users.email], // UPDATE OF when: ({ OLD, NEW }) => sql`${OLD.email} IS DISTINCT FROM ${NEW.email}`, function: setUpdatedAt, }); 

It's a text scan, not a SQL parser, so Postgres still reports anything else when the migration runs. The README now spells out exactly what's checked and what isn't (0.6.1, on npm).

GitHub: https://github.com/Robertpaschal/drizzle-pg-extras
npm: https://www.npmjs.com/package/drizzle-pg-extras