DEV Community

Cover image for Five trigger WHEN clauses Postgres will reject
Odinaka Robert Nnamani
Odinaka Robert Nnamani

Posted on

Five trigger WHEN clauses Postgres will reject

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,
});
Enter fullscreen mode Exit fullscreen mode

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

Top comments (1)

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

Tried 0.6.1: the scanner doesn't know backslash escapes in E'...', so after E'\'' it thinks a string is still open and skips the rest. On an INSERT trigger, NEW.note <> E'\'' OR OLD.status IS NULL passes generate and Postgres rejects it at migrate. Swap in 'x' and the OLD check catches it.