Triggers

@Trigger declares a database trigger on an entity. It fires on every write to the table, whether from uql, a console UPDATE, a data migration or another service. uql types it against the entity, renders it for each SQL engine, and installs it with sync and generated migrations.

This one logs every status change of a post:

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, on update events, fires the trigger only when one of the listed columns changes value. run returns the SQL to run, reading the rows through refs like newRow.status. name is an optional label.

Without decorators, pass the same options in defineEntity’s triggers list, or append one with defineTrigger(Post, { on, run }).

What uql renders

Column names come from the entities, so postId becomes post_id. Each engine fires only when status actually changes:

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"()

SQL Server

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

CockroachDB renders like Postgres. MySQL, MariaDB and SQLite render row-level triggers in their own syntax, with the same change check.

Writing a table: insertInto, upsertInto, updateTable, deleteFrom

These helpers cover most trigger bodies, and insertInto is in the example above. Each is typed by the entity it writes, so an unknown field, a wrongly typed value or a $where on a relation does not compile:

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

upsertInto inserts a row or updates the one matching its conflict paths, like upsertOne. The existing row takes the incoming values unless you pass an update, such as a running total:

@Entity()
export class PostStats {
  @Id({ type: Number }) id?: number;
  @Field({ type: Number, unique: true }) postId?: number | null;
  @Field({ type: Number }) edits?: number | null;
}

@Trigger({
  on: 'afterUpdate',
  run: (newRow) => upsertInto(PostStats, { postId: true }, { postId: newRow.id, edits: 1 }, { edits: { $inc: 1 } }),
})

In that update, SQL can read the existing row (raw`${refs(PostStats).edits} + 1` ), $inc can step by a column of the changed row ({ $inc: newRow.amount }), and {} leaves the row as it is.

A trigger runs inside the database, so uql adds nothing to what you write:

• Only the fields you name are written. onInsert and onUpdate run in JavaScript, so a trigger fills neither, and an insert missing an onInsert field is refused unless the column has a defaultValue. Set it yourself, as SQL if needed: id: raw`gen_random_uuid()` .
• No entity filters apply. deleteFrom on a soft-delete entity removes the row, and security filters are not added, so scope each write by the row’s columns: { tenantId: newRow.tenantId }.
• An update or delete whose $where names no rows is refused, as in the querier.
• SQL Server refuses $inc, $mul and $push: it writes each target row once per statement, so an $inc would miscount when several changed rows match it.

Conditions: where

of checks whether a column changed; where checks anything else. Conditions on the new row go under $new, on the old one under $old, with the usual $where operators. With both, where matches a transition:

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

For anything else, where takes SQL: where: (newRow, oldRow) => raw`${newRow.views} > 2 * ${oldRow.views}` . No soft-delete or security filter is added.

Events and rows

run and a where callback always receive (newRow, oldRow), in that order. Insert events have only newRow, delete events only oldRow, and update events both; reading a row the event lacks is a compile error. Since the positions never change, one body reading only oldRow serves both an update and a delete trigger.

Your own SQL

Any SQL the engine runs can be the body, written with raw. Refs render as columns, and helpers can sit inside the same raw:

@Trigger({
  on: 'afterDelete',
  run: (_newRow, oldRow) =>
    raw`${deleteFrom(PostAudit, { $where: { postId: oldRow.id } })}
        DELETE FROM post_cache;`,
})

uql does not translate your SQL. It must run on every engine you use, or you give a body per engine.

Refusing a write: refuse

refuse(message) fails the write that fired the trigger, on every engine. It keeps a table append-only, or with a where, refuses only some rows:

@Trigger(
  { on: 'beforeDelete', run: () => refuse('ledger entries are kept') },
  { on: 'beforeUpdate', where: { $old: { posted: true } }, run: () => refuse('a posted entry is final') },
)

SQL Server has no before* events. Use afterUpdate and afterDelete there; they still roll the statement back.

Deferred to commit: deferred

On Postgres, deferred: true fires an after* trigger at commit instead of after each statement, so a check sees every write in the transaction. A ledger whose entries must sum to zero can then take them in any order. Other engines refuse it.

@Trigger({
  on: 'afterInsert',
  deferred: true,
  run: {
    postgres: (newRow) =>
      raw`IF (SELECT SUM(amount) FROM ledger_entry WHERE transaction_id = ${newRow.transactionId}) <> 0 THEN
            ${refuse('a ledger transaction must balance')}
          END IF;`,
  },
})

Sync and migrations

sync and generate:entities install the triggers an entity declares and drop the ones it no longer declares. They leave an unchanged trigger alone and replace an edited one, so a second sync runs nothing. A trigger whose name does not start with _uql is never touched.

You can retype or drop a column a trigger watches. Postgres refuses both while the trigger exists, so uql drops the trigger and restores it around the change.

Naming

The installed name is _uql_, the table, your name and a hash of the SQL: name: 'audit' installs as _uql_Post__audit_0a57e9 on Postgres. Without a name, the trigger is named after its event and position.

Engine notes

MySQL

With binary logging on (the MySQL default), a user without SUPER cannot create triggers unless you set log_bin_trust_function_creators = 1, in the parameter group on managed MySQL. MariaDB needs nothing.

SQL Server

SQL Server fires a trigger once per statement, with inserted and deleted tables instead of single rows, and has no before* events. An update pairs the two tables by primary key, so a statement that changes a row’s key leaves that row out of of, where and the body.

The write helpers work there unchanged. Your own SQL reads inserted and deleted whole, so it is refused alongside of or where. To support SQL Server and another engine, give a 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;`,
  },
})

postgres also covers CockroachDB, and mysql covers MariaDB. A map missing the engine in use is refused at sync.

MongoDB

MongoDB has no trigger that runs within a write, so writes to an entity that declares one, a stamp included, are refused. Atlas Database Triggers run after the commit, so they cannot block, roll back with or stamp a write. For logic that only follows uql’s own writes, use a lifecycle hook.