> 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

# Type-safe triggers in an ORM. What?

> UQL declares database triggers on the entity, checks their bodies against your entities, and renders them for PostgreSQL, MySQL, SQLite and SQL Server. Why it matters, how it works, and where it stops.

Source: https://uql-orm.dev/blog/type-safe-triggers

Your audit log is written by a lifecycle hook, and it works in every test. Then someone runs a backfill script, fixes a row in `psql`, or points a second service at the same table, and the log has a gap. Nothing failed. The hook never ran, because an ORM hook only sees the ORM’s own writes.

A database trigger has no such gap: the database runs it on every write, whoever makes it. In UQL you declare the trigger on the entity, the compiler checks its body against your entities, and uql renders it for each SQL engine. As far as I can tell, it is the first TypeScript ORM to do all three.

## Why teams avoid triggers

Many teams avoid triggers, for good reasons. A trigger usually lives as a SQL string (plain text) in an old migration file. Nobody sees it from the model, nothing checks it against the columns, so a renamed column breaks it when it first fires, and every engine wants its own syntax.

Those are tooling problems, not problems with triggers. Here is the same audit log, declared where the table is:

```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;
}
```

`of` fires the trigger only when `status` changes value, and `run` writes the audit row. Both read columns as properties, so the compiler knows every name and type in the body. A `where` adds conditions on either row, such as a transition from `{ $old: { status: 'draft' } }` to `{ $new: { status: 'published' } }`, checked the same way.

## What the compiler catches

Each of these fails at build time, not when the trigger first fires:

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

defineTrigger(Post, {
  on: 'afterInsert',
  run: (_newRow, oldRow) => insertInto(PostAudit, { postId: oldRow.id }), // error: no old row on insert
});

defineTrigger(Post, {
  on: 'afterUpdate',
  run: (newRow) => insertInto(PostAudit, { post: newRow.id }), // error: PostAudit has no `post`
});

defineTrigger(Post, {
  on: 'afterUpdate',
  run: (newRow) => insertInto(PostAudit, { postId: newRow.status }), // error: postId holds a number
});
```

The same goes for watching a column the entity lacks, an `updateTable` or `deleteFrom` with no `$where` (it would hit every row on every fire), and a body for an engine uql does not render triggers for.

## One declaration, every SQL engine

This is what `sync` and generated migrations install for that one decorator. CockroachDB gets the Postgres form, and MariaDB the MySQL one:

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_d8fbff"
ON "Post" AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  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"
    WHERE EXISTS (SELECT deleted."status" EXCEPT SELECT inserted."status");
END
```

Look at the condition in each. “Fire only when `status` changes” must count `NULL` to `'draft'` as a change, which a plain `<>` does not, and each engine spells that differently (`IS DISTINCT FROM`, `<=>`, `IS NOT`, `EXCEPT`). SQL Server also hands the trigger whole tables rather than rows. That is four chances to get it subtly wrong by hand.

## How the other ORMs do it

The same audit trigger, on PostgreSQL only:

Drizzle, Prisma, TypeORM:

```sql
-- No trigger API. This goes in a hand-written migration: `drizzle-kit generate --custom`,
-- `prisma migrate dev --create-only`, or `queryRunner.query(...)` in a TypeORM migration.
CREATE FUNCTION post_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO post_audit (post_id, from_status, to_status)
    VALUES (NEW.id, OLD.status, NEW.status);
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER post_audit AFTER UPDATE OF status ON post
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION post_audit();
```

MikroORM:

```ts title="MikroORM"
// Declared on the entity and installed by migrations. The callback maps property names to
// column names; the body and `when` around them are SQL strings, one set per dialect.
import { defineEntity, p } from '@mikro-orm/core';

const PostSchema = defineEntity({
  name: 'Post',
  properties: {
    id: p.integer().primary(),
    status: p.string().nullable(),
  },
  triggers: [
    {
      name: 'post_audit',
      timing: 'after',
      events: ['update'],
      when: 'OLD.status IS DISTINCT FROM NEW.status',
      body: (c) =>
        `INSERT INTO post_audit (post_id, from_status, to_status)
         VALUES (NEW.${c.id}, OLD.${c.status}, NEW.${c.status}); RETURN NULL;`,
    },
  ],
});
```

MikroORM comes closest: the trigger lives on the entity, and the column names come from its metadata. The SQL around them is still a string, so a wrong type or an `OLD` read on an insert reaches the database unchecked, and a second engine means a second body. TypeORM’s subscribers, like hooks, only see TypeORM’s own writes.

## Keeping triggers in sync

`sync` and `generate:entities` install what an entity declares and drop what it no longer declares. Each installed name ends in a hash of its SQL, so an unchanged trigger is left alone and an edited one is replaced. Triggers uql did not create are never touched.

## Where it stops

- **MongoDB has no triggers** that run inside a write, so a write to an entity that declares one is refused rather than made without it.
- **Raw SQL bodies are not translated.** A `raw` body has to run on every engine you use, or you give one per engine.
- **No entity filters apply inside a trigger.** It runs outside any request, so soft-delete and `security` filters do not scope its writes; scope them by the row’s own columns.

SQL Server and MySQL each have a few rules of their own, listed on the [Triggers](https://uql-orm.dev/entities/triggers.md) page.

## Why it matters more with coding agents

Coding agents now write much of the code that touches a database, including the kind hooks never see: a one-off script, a backfill, a quick `UPDATE`. A rule the database enforces holds for all of it. And a trigger on the entity sits in the file the agent reads and is checked by the compiler it runs; a trigger in an old migration is neither.

## Hook, trigger or stamp

| You need | Use |
| - | - |
| Logic around UQL’s own writes: call an API, publish an event, validate | a [lifecycle hook](https://uql-orm.dev/entities/lifecycle-hooks.md) |
| A row the database must write whoever changes the table, like an audit entry | a [trigger](https://uql-orm.dev/entities/triggers.md) |
| A column the database keeps current on every write, like `updatedAt` | a [stamp](https://uql-orm.dev/entities/computed-fields.md#stamps-stored-on-an-event) |

A stamp is a trigger whose body uql writes for you: ``@Field({ computed: raw`CURRENT_TIMESTAMP`, stored: ['update'] })``.

## Get started

- **[Triggers](https://uql-orm.dev/entities/triggers.md)**: every option, the rendered SQL per engine, and the engine notes
- **[Comparison](https://uql-orm.dev/comparison.md)**: triggers and the rest of the feature matrix, ORM by ORM
- **[GitHub](https://github.com/rogerpadilla/uql)**

If a trigger you need cannot be declared this way, open an issue with the SQL you would have written.