Database Triggers

Overview

SyndrDB supports BEFORE and AFTER triggers that fire automatically in response to INSERT, UPDATE, and DELETE operations. Triggers execute user-defined SyndrQL commands, enabling validation, audit logging, cascading updates, and other automated behaviors.

  • BEFORE triggers — Fire before the operation; can modify the new document or abort the operation
  • AFTER triggers — Fire after the operation has been committed; errors are logged but do not abort

Table of Contents

CREATE TRIGGER

Full Syntax

CREATE TRIGGER "trigger_name" ON BUNDLE "bundle_name"
    BEFORE|AFTER INSERT[, UPDATE[, DELETE]]
    FOR EACH DOCUMENT
    [WHEN (condition)]
    [PRIORITY n]
    EXECUTE (
        trigger_body
    )

Simple Example

CREATE TRIGGER "set_updated_at" ON BUNDLE "users"
    BEFORE UPDATE
    FOR EACH DOCUMENT
    EXECUTE (
        SET NEW "updated_at" = F:NOW()
    )

Trigger Timing

Timing When It Fires Can Modify Data? Can Abort? Error Behavior
BEFORE Before the operation is applied Yes (via SET NEW) Yes (return error) Aborts the operation
AFTER After the operation has been committed No No Logged but non-fatal

Trigger Events

Triggers can fire on one or more DML events. Events can be combined:

-- Single event
BEFORE INSERT

-- Multiple events
BEFORE INSERT, UPDATE

-- All three events
AFTER INSERT, UPDATE, DELETE
Event OLD Available? NEW Available? Description
INSERT No Yes Fires when a new document is added
UPDATE Yes Yes Fires when an existing document is modified
DELETE Yes No Fires when a document is removed

OLD and NEW References

Within a trigger body, you can reference the document before and after the operation using qualified identifiers:

  • OLD."field_name" — The field value before the operation (available for UPDATE and DELETE)
  • NEW."field_name" — The field value after the operation (available for INSERT and UPDATE)

Reading Values

-- Use OLD/NEW in conditions
WHEN (NEW."status" != OLD."status")

-- Use OLD/NEW in the trigger body
ADD DOCUMENT TO BUNDLE "audit_log" WITH ({
    "old_value" = OLD."email",
    "new_value" = NEW."email"
})

Modifying NEW (BEFORE Triggers Only)

In a BEFORE trigger, you can modify the document that will be written using SET NEW:

-- Set a field on the new document
SET NEW "updated_at" = F:NOW()
SET NEW "status" = "processed"

Important: SET NEW is only allowed in BEFORE triggers. Using it in an AFTER trigger will produce a validation error.

WHEN Clause

The optional WHEN clause adds a condition that must be true for the trigger to fire. If the condition evaluates to false, the trigger is skipped for that document.

-- Only fire when status changes
CREATE TRIGGER "status_change_log" ON BUNDLE "orders"
    AFTER UPDATE
    FOR EACH DOCUMENT
    WHEN (NEW."status" != OLD."status")
    EXECUTE (
        ADD DOCUMENT TO BUNDLE "status_history" WITH ({
            "order_id" = NEW."order_id",
            "old_status" = OLD."status",
            "new_status" = NEW."status",
            "changed_at" = F:NOW()
        })
    )

Priority

When multiple triggers are defined for the same event on the same bundle, the PRIORITY value determines the execution order. Lower numbers fire first.

-- This fires first (priority 100)
CREATE TRIGGER "validate_email" ON BUNDLE "users"
    BEFORE INSERT
    FOR EACH DOCUMENT
    PRIORITY 100
    EXECUTE (...)

-- This fires second (priority 200)
CREATE TRIGGER "set_defaults" ON BUNDLE "users"
    BEFORE INSERT
    FOR EACH DOCUMENT
    PRIORITY 200
    EXECUTE (...)

The default priority is 1000.

DROP TRIGGER

Removes a trigger from a bundle.

DROP TRIGGER "trigger_name" ON BUNDLE "bundle_name"

Example

DROP TRIGGER "set_updated_at" ON BUNDLE "users"

ENABLE / DISABLE TRIGGER

Temporarily enable or disable a trigger without dropping it.

-- Disable a trigger
DISABLE TRIGGER "set_updated_at" ON BUNDLE "users"

-- Re-enable it
ENABLE TRIGGER "set_updated_at" ON BUNDLE "users"

Disabled triggers remain in the bundle metadata but do not fire. This is useful for bulk data loads or maintenance operations where trigger overhead is not desired.

Body Restrictions

The trigger body is executed within the same transaction context as the triggering operation. The following restrictions apply:

  • No DDL statements — Cannot CREATE, DROP, or ALTER bundles/indexes within a trigger
  • No transaction control — Cannot use BEGIN, COMMIT, or ROLLBACK within a trigger
  • No SET NEW in AFTER triggers — Only BEFORE triggers can modify the incoming document
  • Maximum body length — 65,536 characters
  • Maximum trigger name length — 128 characters

Recursion and Cascade Depth

A trigger can cause another trigger to fire (cascading). SyndrDB limits cascade depth to prevent infinite loops.

Setting Default Description
TriggerMaxDepth 16 Maximum cascade depth before aborting
TriggerAllowRecursion false Whether a trigger can fire itself (direct recursion)

If the maximum depth is exceeded, the operation is aborted with a recursion error.

Configuration Settings

Setting Default Description
TriggersEnabled true Global toggle to enable/disable all triggers
TriggerMaxDepth 16 Maximum cascade depth
TriggerMaxPerBundle 100 Maximum number of triggers per bundle
TriggerTimeoutMs 5000 Maximum execution time per trigger in milliseconds
TriggerAllowRecursion false Allow a trigger to invoke itself

Complete Examples

Example 1: Audit Log

-- Log all changes to the users bundle
CREATE TRIGGER "audit_user_changes" ON BUNDLE "users"
    AFTER UPDATE
    FOR EACH DOCUMENT
    EXECUTE (
        ADD DOCUMENT TO BUNDLE "audit_log" WITH ({
            "bundle" = "users",
            "action" = "UPDATE",
            "old_email" = OLD."email",
            "new_email" = NEW."email",
            "changed_at" = F:NOW()
        })
    )

Example 2: Data Validation

-- Ensure price is never negative
CREATE TRIGGER "validate_price" ON BUNDLE "products"
    BEFORE INSERT, UPDATE
    FOR EACH DOCUMENT
    WHEN (NEW."price" < 0)
    PRIORITY 100
    EXECUTE (
        SET NEW "price" = 0
    )

Example 3: Auto-Set Timestamps

-- Automatically set created_at on INSERT
CREATE TRIGGER "set_created_at" ON BUNDLE "orders"
    BEFORE INSERT
    FOR EACH DOCUMENT
    PRIORITY 100
    EXECUTE (
        SET NEW "created_at" = F:NOW()
    )

-- Automatically set updated_at on UPDATE
CREATE TRIGGER "set_updated_at" ON BUNDLE "orders"
    BEFORE UPDATE
    FOR EACH DOCUMENT
    PRIORITY 100
    EXECUTE (
        SET NEW "updated_at" = F:NOW()
    )

Example 4: Cascading Update

-- When a user is deactivated, cancel their pending orders
CREATE TRIGGER "deactivate_user_orders" ON BUNDLE "users"
    AFTER UPDATE
    FOR EACH DOCUMENT
    WHEN (OLD."active" == TRUE AND NEW."active" == FALSE)
    EXECUTE (
        UPDATE DOCUMENTS IN BUNDLE "orders" (status = "cancelled") CONFIRMED
            WHERE customer_id == NEW."user_id" AND status == "pending"
    )

Example 5: Delete Logging

-- Log all deletions
CREATE TRIGGER "log_deletions" ON BUNDLE "documents"
    AFTER DELETE
    FOR EACH DOCUMENT
    EXECUTE (
        ADD DOCUMENT TO BUNDLE "deletion_log" WITH ({
            "deleted_id" = OLD."doc_id",
            "deleted_title" = OLD."title",
            "deleted_at" = F:NOW()
        })
    )

Quick Reference

Command Description
CREATE TRIGGER "name" ON BUNDLE "b" ... Create a new trigger
DROP TRIGGER "name" ON BUNDLE "b" Remove a trigger
ENABLE TRIGGER "name" ON BUNDLE "b" Re-enable a disabled trigger
DISABLE TRIGGER "name" ON BUNDLE "b" Temporarily disable a trigger
SET NEW "field" = value Modify the incoming document (BEFORE only)
OLD."field" Reference pre-operation field value
NEW."field" Reference post-operation field value

Last updated: March 2026