Triggers

A trigger runs a database function automatically when rows of a table change, inside the same transaction as the change. If the function raises an error, the change is rolled back. Use triggers to keep data consistent: fill updated_at, write an audit log, keep a counter in step, or refuse an invalid change.

#Trigger, webhook or function trigger?

Triggers run for every write, including ones made over a direct Postgres connection. Webhooks and function triggers run for writes made through the API.

#In the console

Schema › Triggers groups triggers by table. Each row shows when it runs (before or after), the events, whether it runs per row or per statement, the function it calls and its condition. The switch turns a trigger off or on without deleting it. Click a trigger for its full definition; the function name opens it under Functions.

#Create a trigger

1. 1

   Write the trigger function

   Under Schema › Functions, create a function that returns trigger (language PL/pgSQL). The form fills in a template.

2. 2

   New trigger

   Under Schema › Triggers, click New trigger and fill in the fields below. Only functions that return trigger are offered.

3. 3

   Check, then create

   Check runs it in a rolled-back transaction; Create trigger applies it as a recorded migration you can undo from Version history: see, undo and restore changes.

#Examples

create or replace function touch_updated_at()
returns trigger
language plpgsql
as $
begin
  new.updated_at := now();
  return new;
end;
$;

create trigger orders_touch_updated_at
before update on orders
for each row execute function touch_updated_at();

#NEW, OLD and what to return

• A before row trigger returns the row to write: return NEW (changed or not) to go ahead, or NULL to skip this row silently. For delete, return OLD to go ahead.
• An after trigger's return value is ignored; return NULL.
• Raising an exception refuses the whole statement, and the caller gets your message and error code.
• TG_OP (INSERT, UPDATE, DELETE, TRUNCATE), TG_TABLE_NAME and TG_TABLE_SCHEMA let one function serve several tables or events.

#Turn a trigger off

The switch on each row runs alter table … disable trigger … (or enable) as a recorded migration, so turning it back on is one click or an undo in Version History. Turn a trigger off for a bulk import or a backfill, then on again. Deleting a trigger keeps its definition in the undo, including whether it was off.

#Good practice

• Keep trigger functions short: they run once per changed row, inside the caller's transaction, and the statement timeout covers them.
• Don't call slow or external things from a trigger. Use a webhook or a function trigger, which run after commit and retry.
• A trigger that writes to another table can fire that table's triggers. Guard against loops with a condition, or pg_trigger_depth() < 2.
• Trigger functions run as the caller unless they are security definer, so the caller needs access to every table they write (Database functions).
• Sovaria's own triggers for table history (Table history (time travel)) and automatic embeddings aren't listed and don't count against your limit; turn those features off in their settings instead.

#Limits

#Errors

#Coming from Firebase or Supabase?

Moving an existing app? Migrate from Supabase or Firebase imports users (with their passwords), data, files and rules.