Menu

Five trigger WHEN clauses Postgres will reject
📰

Five trigger WHEN clauses Postgres will reject

DEV Community: webdev·Odinaka Robert Nnamani·about 9 hours ago
#UdKT7Zp7
#devtowebdev#devto#dev#trigger#migration
Reading 0:00
15s threshold

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 OLD or NEW in 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 .…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.