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.

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, reading each row through refs like newRow.status. name is an optional label, covered in Naming.

Without decorators, the same options go in defineEntity’s triggers, or one at a time through defineTrigger:

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

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

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

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

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:

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:

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.

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.

@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:

@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:

@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:

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:

@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.

Next Steps

• Computed Fields: a stamp, stored: ['update'], is a trigger whose body uql writes for you, for a value the database has to keep writing.
• Lifecycle Hooks: the JavaScript equivalent, for logic that only has to run around uql’s own writes.