# Triggers

`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](/docs/define/schema/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.

```ts title="call.ts"
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, // [!code focus]
      timing: "before", // [!code focus]
      events: ["update"], // [!code focus]
      level: "row", // [!code focus]
      calls: touch, // [!code focus]
    }),
  ],
});
```

<Callout type="error" title="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.`
</Callout>

<Callout type="error" title="OKM1023">
The same name on the same table is `Duplicate identity for trigger notes_touch.`
</Callout>

### 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](/docs/define/schema/casing) `"snake"`, `updatedAt` is `updated_at`.

```ts title="update-of.ts"
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"], // [!code focus]
      calls: touch,
    }),
  ],
});
```

<Callout type="error" title="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.`
</Callout>

### when

`when` is optional SQL, stored as written and rendered `when ( … )`.

```ts title="when.ts"
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`, // [!code focus]
      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.

```ts title="together.ts"
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", // [!code focus]
  functions: [touch],
  triggers: [
    trigger("notes_touch", {
      on: notes,
      timing: "before",
      events: ["insert", "update"], // [!code focus]
      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](/docs/define/schema/functions). `timestamps({ enforce: "trigger" })` adds its own function and trigger; that option is on [traits](/docs/define/schema/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"()`.

```ts title="own.ts"
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, // [!code focus]
      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"()`.

```ts title="own-advanced.ts"
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"], // [!code focus]
      level: "row",
      updateOf: ["title"], // [!code focus]
      when: sql`new.title <> ''`, // [!code focus]
      calls: touch,
    }),
  ],
});
```

<Callout type="error" title="OKM1020">
A column the table does not have is `Trigger notes_touch names column missing, which notes does not have.`
</Callout>