Postgres will reject some trigger WHEN clauses, but only when the migration runs.
A few rules that catch people out:
- A statement-level trigger can't read
OLDorNEWin its WHEN. There's no single row to compare. - An INSERT trigger can't read
OLD. There is no old row. - A DELETE trigger can't read
NEW. There is no new row. - An INSTEAD OF trigger on a view can't have a WHEN at all.
- 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