> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Triggers

> Declare database triggers on the entity with @Trigger, for every SQL engine. They fire on insert, update or delete no matter who writes to the table.

Source: https://uql-orm.dev/entities/triggers

`@Trigger` declares a database trigger, so it fires no matter who writes to the table: uql, a raw `UPDATE` run by hand, a data migration, or another service on the same database.

It is typed against the entity, adapted to each SQL engine, and installed or dropped by `sync` and generated migrations as the entity changes.

```ts
import { Entity, Field, Id, insertInto, Trigger } from 'uql-orm';

@Entity()
export class PostAudit {
  @Id({ type: Number })
  id?: number;

  @Field({ type: Number, name: 'post_id', nullable: false })
  postId?: Post['id'];

  @Field({ type: String, name: 'from_status' })
  fromStatus?: Post['status'];

  @Field({ type: String, name: 'to_status' })
  toStatus?: Post['status'];
}

@Trigger({
  on: 'afterUpdate',
  name: 'audit',
  of: (post) => [post.status],
  run: (newRow, oldRow) =>
    insertInto(PostAudit, {
      postId: newRow.id,
      fromStatus: oldRow.status,
      toStatus: newRow.status,
    }),
})
@Entity()
export class Post {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  status?: string | null;

  @Field({ type: Number })
  views?: number | null;
}
```

`on` is the event. `of` narrows it to the listed columns: the trigger fires only when one of them changes value, not when an update sets it to the value it already had. `run` returns the body, the SQL the trigger executes: a write helper as here, or [your own SQL](#your-own-sql), reading each row through refs like `newRow.status`. `name` is an optional label, covered in [Naming](#naming).

Without decorators, the same options go in [`defineEntity`](https://uql-orm.dev/entities/imperative.md)’s `triggers`, or one at a time through `defineTrigger`:

```ts
import { defineEntity, defineTrigger, deleteFrom, insertInto } from 'uql-orm';

export class Post {
  id?: number;
  status?: string | null;
}

defineEntity(Post, {
  fields: {
    id: { type: Number, isId: true },
    status: { type: String },
  },
  triggers: [
    {
      on: 'afterUpdate',
      name: 'audit',
      of: (post) => [post.status],
      run: (newRow, oldRow) =>
        insertInto(PostAudit, {
          postId: newRow.id,
          fromStatus: oldRow.status,
          toStatus: newRow.status,
        }),
    },
  ],
});

// or piece by piece, appended in the order registered
defineTrigger(Post, {
  on: 'afterDelete',
  name: 'purge',
  run: (_newRow, oldRow) =>
    deleteFrom(PostAudit, { $where: { postId: oldRow.id } }),
});
```

Every option below works the same in all three forms.

## What uql renders for you

This is the trigger above as uql renders it for each engine. Table and column names come from the entities, so `postId` becomes `post_id`:

PostgreSQL:

```sql
CREATE OR REPLACE FUNCTION "_uql_Post__audit_0a57e9"() RETURNS trigger AS $uql$
BEGIN
  INSERT INTO "PostAudit" ("post_id", "from_status", "to_status")
    VALUES (NEW."id", OLD."status", NEW."status");
  RETURN NULL;
END $uql$ LANGUAGE plpgsql;

CREATE TRIGGER "_uql_Post__audit_0a57e9"
AFTER UPDATE OF "status" ON "Post"
FOR EACH ROW
WHEN (OLD."status" IS DISTINCT FROM NEW."status")
EXECUTE FUNCTION "_uql_Post__audit_0a57e9"()
```

MySQL:

```sql
CREATE TRIGGER `_uql_Post__audit_94c077`
AFTER UPDATE ON `Post`
FOR EACH ROW
BEGIN
  IF NOT (OLD.`status` <=> NEW.`status`) THEN
    INSERT INTO `PostAudit` (`post_id`, `from_status`, `to_status`)
      VALUES (NEW.`id`, OLD.`status`, NEW.`status`);
  END IF;
END
```

SQLite:

```sql
CREATE TRIGGER `_uql_Post__audit_08d35c`
AFTER UPDATE OF `status` ON `Post`
FOR EACH ROW
WHEN (OLD.`status` IS NOT NEW.`status`)
BEGIN
  INSERT INTO `PostAudit` (`post_id`, `from_status`, `to_status`)
    VALUES (NEW.`id`, OLD.`status`, NEW.`status`);
END
```

SQL Server:

```sql
CREATE TRIGGER "_uql_Post__audit_6c40ce"
ON "Post" AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (
    SELECT 1 FROM inserted JOIN deleted ON inserted."id" = deleted."id"
    WHERE EXISTS (SELECT deleted."status" EXCEPT SELECT inserted."status")
  )
  BEGIN
    INSERT INTO "PostAudit" ("post_id", "from_status", "to_status")
      SELECT inserted."id", deleted."status", inserted."status"
      FROM inserted JOIN deleted ON inserted."id" = deleted."id";
  END
END
```

Each engine gets its own SQL, with the same behavior: the trigger fires only when `status` changes value. SQL Server passes `inserted` and `deleted` as tables rather than rows, so uql joins them, both to compare the values and to write from them. CockroachDB renders like Postgres, and MariaDB like MySQL.

## Sync and migrations

`sync` and `generate:entities` install the triggers an entity declares and drop the ones it no longer declares, whether the table is new or already exists. A trigger uql did not create, one without the `_uql` prefix, is never touched.

Each installed name ends in a hash of the trigger’s SQL, so uql can tell what changed without storing anything. An unchanged trigger keeps its name and is left alone: a second `sync` has nothing to run, and `generate:entities` writes no migration. An edited trigger gets a new name, so uql creates it and drops the old one.

You can still retype or drop a column a trigger watches. Postgres refuses both while the trigger exists, so uql removes the table’s triggers around the change and puts them back after.

## Writing a table: `insertInto`, `updateTable`, `deleteFrom`

These three cover the writes a body makes most. Each is typed by the entity it writes, so an unknown field, a value or a row’s ref of the wrong type, or a `$where` on a relation does not compile:

```ts
import { deleteFrom, insertInto, Trigger, updateTable } from 'uql-orm';

@Trigger(
  {
    on: 'afterInsert',
    run: (newRow) => insertInto(PostAudit, { postId: newRow.id, toStatus: newRow.status }),
  },
  {
    on: 'afterUpdate',
    run: (newRow) => updateTable(PostAudit, { $where: { postId: newRow.id } }, { toStatus: newRow.status }),
  },
  {
    on: 'afterDelete',
    run: (_newRow, oldRow) => deleteFrom(PostAudit, { $where: { postId: oldRow.id } }),
  },
)
```

They look like the querier’s writes, but a trigger runs inside the database, outside any request, so uql adds nothing to what you write:

- **Only the fields you name.** `onInsert` and `onUpdate` run in JavaScript, so a trigger fills neither. An insert that leaves out an `onInsert` field is refused unless its column has a `defaultValue`; set the field yourself, as SQL if it has to be computed: ``id: raw`gen_random_uuid()` ``on Postgres.
- **No entity filters.** Filters resolve per request, and a trigger has no request. So `deleteFrom` on a soft-delete entity removes the row instead of stamping it, and security filters do not apply: scope each write by the row’s own columns, like `{ tenantId: newRow.tenantId }`.
- **No whole-table writes.** An update or delete whose `$where` names no rows is refused, as in the querier, since it would hit every row each time the trigger fires.
- **No `$inc`, `$mul` or `$push` on SQL Server.** There a trigger updates through `UPDATE ... FROM inserted`, which writes each target row once however many changed rows match it. An `$inc` would apply once where other engines apply it per row, so uql refuses all three rather than miscount.

## Your own SQL

`run` returns SQL, so anything the engine runs can be the body, written in `raw`: each ref renders as its column, SQL reading none is emitted as written, and a helper joins in the same `raw`, still rendered for the engine:

```ts
import { deleteFrom, raw, Trigger } from 'uql-orm';

@Trigger(
  {
    on: 'afterUpdate',
    of: (post) => [post.status],
    run: (newRow) => raw`PERFORM pg_notify('post_status', ${newRow.id}::text);`,
  },
  { on: 'afterInsert', run: () => raw`DELETE FROM post_cache;` },
  {
    on: 'afterDelete',
    run: (_newRow, oldRow) =>
      raw`${deleteFrom(PostAudit, { $where: { postId: oldRow.id } })}
          DELETE FROM post_cache;`,
  },
)
```

uql does not translate the SQL you write, so it has to run on every engine you use, or you give a [body per engine](#sql-server).

## `where`: extra conditions

`of` checks whether a column changed. `where` checks anything else about the rows `run` reads: conditions on the new row under `$new` and on the old one under `$old`, with the operators a query’s `$where` takes. Only what you write applies; no soft-delete or security filter is added.

```ts
@Trigger({
  on: 'afterInsert',
  where: { $new: { views: { $gt: 100 } } },
  run: (newRow) => insertInto(PostAudit, { postId: newRow.id, toStatus: 'trending' }),
})
```

With both rows, `where` can state a transition, which `of` cannot: that a value changed, and from what to what:

```ts
@Trigger({
  on: 'afterUpdate',
  where: { $old: { status: 'draft' }, $new: { status: { $in: ['published', 'featured'] } } },
  run: (newRow, oldRow) => insertInto(PostAudit, { postId: newRow.id, fromStatus: oldRow.status }),
})
```

When a predicate cannot say it, `where` takes SQL over the rows instead:

```ts
@Trigger({
  on: 'afterUpdate',
  where: (newRow, oldRow) => raw`${newRow.views} > 2 * ${oldRow.views}`,
  run: (newRow) => insertInto(PostAudit, { postId: newRow.id, toStatus: 'surging' }),
})
```

## Events and what `run` reads

`run` and a `where` callback receive the SQL standard’s `NEW ROW` and `OLD ROW`, always in that order: `(newRow, oldRow)`. A `where` object names them `$new` and `$old`.

| `on` | has |
| - | - |
| `beforeInsert`, `afterInsert` | `newRow` |
| `beforeUpdate`, `afterUpdate` | `newRow` and `oldRow` |
| `beforeDelete`, `afterDelete` | `oldRow` |

Reading a row the event does not have is a compile error. The rows keep their positions on every event, so a body that reads only `oldRow` serves an update and a delete alike:

```ts
import { Entity, Field, Id, insertInto, type RefMap, Trigger } from 'uql-orm';

const archive = (_newRow: RefMap<Post>, oldRow: RefMap<Post>) =>
  insertInto(PostAudit, { postId: oldRow.id, fromStatus: oldRow.status });

@Trigger(
  { on: 'afterUpdate', name: 'archiveUpdate', run: archive },
  { on: 'afterDelete', name: 'archiveDelete', run: archive },
)
@Entity()
export class Post {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  status?: string | null;
}
```

## MySQL

MySQL turns binary logging on by default, and with it on, refuses to create a trigger for a user without `SUPER` unless you set `log_bin_trust_function_creators = 1` (on a managed MySQL, in its parameter group). MariaDB leaves the binary log off by default, so it needs nothing.

## SQL Server

SQL Server fires a trigger once per statement, not once per row, handing the body the `inserted` and `deleted` tables rather than one row each. It has no `BEFORE` triggers and no per-row condition, so `before*` events and `where` are refused there; check inside the body instead. An update pairs `inserted` with `deleted` by primary key, the only link SQL Server offers, so a statement that changes a row’s key leaves that row out of `of` and out of the body’s writes.

Bodies written with `insertInto`, `updateTable` and `deleteFrom` already read those tables, as the rendered SQL above shows, so they run there unchanged. Raw SQL is not translated, so a project running SQL Server alongside another engine gives one body per engine:

```ts
@Trigger({
  on: 'afterInsert',
  run: {
    postgres: (newRow) => raw`PERFORM pg_notify('post_status', ${newRow.id}::text);`,
    mssql: () => raw`INSERT INTO post_outbox (post_id) SELECT id FROM inserted;`,
  },
})
```

The `postgres` body also covers CockroachDB, and `mysql` covers MariaDB. An empty map does not compile, and one missing the engine in use is refused at `sync`.

## Naming

`name` labels the trigger within its entity. uql builds the installed name from a `_uql_` prefix, the table, your label and a hash of the SQL: `name: 'audit'` above installs as `_uql_Post__audit_0a57e9` on Postgres. The prefix is how uql tells its own triggers from hand-written ones, and the table keeps two entities with the same label apart on engines that scope trigger names to the schema rather than the table. Without a `name`, the trigger is named after its event and position.

## MongoDB

MongoDB runs no trigger within a write, so a write to an entity that declares one, a stamp included, is refused rather than made without it.

Atlas Database Triggers are not a substitute: they read a change stream after the write commits, on Atlas’s own compute, so they cannot hold a row back, roll back with it or stamp it in the same write, and a suspended one skips the events it missed. They exist only on Atlas, not on a self-managed server. For logic that only has to follow uql’s own writes, use a [lifecycle hook](https://uql-orm.dev/entities/lifecycle-hooks.md).

## Next Steps

- [Computed Fields](https://uql-orm.dev/entities/computed-fields.md): a stamp, `stored: ['update']`, is a trigger whose body uql writes for you, for a value the database has to keep writing.
- [Lifecycle Hooks](https://uql-orm.dev/entities/lifecycle-hooks.md): the JavaScript equivalent, for logic that only has to run around uql’s own writes.