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
Top comments (1)
Tried 0.6.1: the scanner doesn't know backslash escapes in
E'...', so afterE'\''it thinks a string is still open and skips the rest. On an INSERT trigger,NEW.note <> E'\'' OR OLD.status IS NULLpasses generate and Postgres rejects it at migrate. Swap in'x'and the OLD check catches it.