Triggers
Triggers. Identity is the table plus the name.
triggers is a list of trigger() from okmodel/fn. Identity is the table plus the name. The function it calls has to be in functions. The catalog record is built when the catalog is first read.
on, timing, events, level, and calls are required. timing is "before", "after", or "instead". "instead" is written instead of. events is "insert", "update", "delete", or "truncate", stored in that order. level is "row" or "statement".
call
One before-update row trigger. calls is a function in this schema.
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
updatedAt: t.timestamptz().defaultSql("now()"),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin new."updatedAt" = now(); return new; end`,
dependsOn: [notes],
});
export default schema({
tables: [notes],
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["update"],
level: "row",
calls: touch,
}),
],
});OKM1020
A function that is not in the catalog is Trigger notes_touch calls touch_notes, which is not in the catalog. No event is Trigger notes_touch needs an event. Another timing is Trigger notes_touch timing deferrable is not supported. Another level is Trigger notes_touch level each is not supported.
OKM1023
The same name on the same table is Duplicate identity for trigger notes_touch.
update of
updateOf is SQL column names for UPDATE OF. Each one is a column dependency. The trigger needs an update event and level: "row". With casing "snake", updatedAt is updated_at.
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
updatedAt: t.timestamptz().defaultSql("now()"),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin new.updated_at = now(); return new; end`,
dependsOn: [{ table: notes, column: "updated_at" }],
});
export default schema({
tables: [notes],
casing: "snake",
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["update"],
level: "row",
updateOf: ["updated_at"],
calls: touch,
}),
],
});OKM1020
A column the table does not have is Trigger notes_touch names column missing, which notes does not have. Columns without an update event are Trigger notes_touch lists UPDATE OF columns without an update event. Statement level is Trigger notes_touch UPDATE OF columns need level row.
when
when is optional SQL, stored as written and rendered when ( … ).
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin return new; end`,
dependsOn: [notes],
});
export default schema({
tables: [notes],
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["update"],
level: "row",
when: sql`old.title is distinct from new.title`,
calls: touch,
}),
],
});together
Several events, updateOf, and when. The column name is the SQL name. This trigger also fires on insert, so when names new only.
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
updatedAt: t.timestamptz().defaultSql("now()"),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin new.updated_at = now(); return new; end`,
dependsOn: [{ table: notes, column: "updated_at" }],
});
export default schema({
tables: [notes],
casing: "snake",
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["insert", "update"],
level: "row",
updateOf: ["updated_at"],
when: sql`new.title <> ''`,
calls: touch,
}),
],
});Create your own
There is no built-in trigger on this list. trigger() from okmodel/fn is the declaration. The function it calls has to be in functions. timestamps({ enforce: "trigger" }) adds its own function and trigger; that option is on traits.
Simple
One before-update row trigger. okm generate writes the table, then the function, then create trigger "notes_touch" before update on "public"."notes" for each row execute function "public"."touch_notes"().
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin return new; end`,
dependsOn: [notes],
});
export default schema({
tables: [notes],
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["update"],
level: "row",
calls: touch,
}),
],
});Advanced
updateOf limits the update to those SQL columns. Insert is still an event, so the trigger also runs then, and updateOf applies only to the update. when is stored as written. This trigger fires on insert, and an insert has no old row, so when names new only. okm generate writes create trigger "notes_touch" before insert or update of "title" on "public"."notes" for each row when (new.title <> '') execute function "public"."touch_notes"().
import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";
const notes = table("notes", {
id: t.identity(),
title: t.text(),
});
const touch = fn("touch_notes", {
returns: "trigger",
language: "plpgsql",
body: sql`begin return new; end`,
dependsOn: [notes],
});
export default schema({
tables: [notes],
functions: [touch],
triggers: [
trigger("notes_touch", {
on: notes,
timing: "before",
events: ["insert", "update"],
level: "row",
updateOf: ["title"],
when: sql`new.title <> ''`,
calls: touch,
}),
],
});OKM1020
A column the table does not have is Trigger notes_touch names column missing, which notes does not have.