Triggers

A trigger tells Postgres to run a function whenever rows in a table are inserted, updated or deleted. Because it runs inside the database, it fires for every change, whether that change came from the Data API, Studio, a cron job or a migration. That makes triggers the right place for rules that must always hold, such as keeping a timestamp current or writing an audit record.

How a trigger is built

A trigger has two parts:

1. A trigger function: a function that returns trigger, usually in PL/pgSQL.
2. The trigger itself, which attaches the function to a table and says when it runs.

Inside a row-level trigger function you get NEW (the row after the change, for insert and update), OLD (the row before, for update and delete) and TG_OP ('INSERT', 'UPDATE' or 'DELETE').

Example: keep updated_at current

A recipe app wants updated_at set on every edit, without trusting clients to send it.

alter table public.recipes add column updated_at timestamptz not null default now();

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

create trigger recipes_touch_updated_at
  before update on public.recipes
  for each row
  execute function public.touch_updated_at();

A before trigger must return the row to write. Returning null cancels the change for that row.

Example: create a profile when a user signs up

Auth stores users in auth.users, which isn't exposed through the Data API. Most apps keep a public profiles table and fill it with a trigger on auth.users.

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text,
  avatar_url text
);
alter table public.profiles enable row level security;

create policy "Profiles are readable by signed-in users"
  on public.profiles for select to authenticated using (true);

create policy "Users edit their own profile"
  on public.profiles for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, new.raw_user_meta_data ->> 'display_name');
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function public.handle_new_user();

security definer is needed because the insert is made by the Auth service's own role, which has no rights on your public tables.

Pass the extra data at sign-up:

await supabase.auth.signUp({
  email: '[email protected]',
  password: 'a long passphrase',
  options: { data: { display_name: 'Sam the cook' } },
})

If this trigger fails (a typo, a not null column you didn't fill, a missing grant), the whole sign-up fails and the client sees "Database error saving new user". Keep the function small and test it with a manual insert first. See Database error saving new user.

Example: an audit log

Record who changed what on invoices:

create schema if not exists private;

create table private.invoice_audit (
  id bigint generated always as identity primary key,
  invoice_id uuid,
  op text not null,
  changed_by uuid,
  old_row jsonb,
  new_row jsonb,
  changed_at timestamptz not null default now()
);

create or replace function private.log_invoice_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into private.invoice_audit (invoice_id, op, changed_by, old_row, new_row)
  values (
    coalesce(new.id, old.id),
    tg_op,
    auth.uid(),
    case when tg_op in ('UPDATE', 'DELETE') then to_jsonb(old) end,
    case when tg_op in ('INSERT', 'UPDATE') then to_jsonb(new) end
  );
  return null;  -- ignored for after triggers
end;
$$;

create trigger invoices_audit
  after insert or update or delete on public.invoices
  for each row
  execute function private.log_invoice_change();

The audit table lives in private, which the Data API doesn't expose, so users can't read or edit the log. auth.uid() still works inside the trigger because the request's JWT claims are available for the whole transaction.

Managing triggers

-- list triggers on a table
select tgname, tgenabled
from pg_trigger
where tgrelid = 'public.invoices'::regclass and not tgisinternal;

-- pause a trigger during a bulk import, then turn it back on
alter table public.invoices disable trigger invoices_audit;
alter table public.invoices enable trigger invoices_audit;

-- remove it
drop trigger if exists invoices_audit on public.invoices;

Studio also lists triggers under Database → Triggers.

Common mistakes

• NEW is null. In a delete trigger only OLD is set; in a statement-level trigger neither is. See NEW is null in a trigger function.
• Changing NEW in an after trigger. It has no effect; use before to modify the row.
• Triggers that fire themselves. An after update trigger that updates the same table fires again, and again. Set values in a before trigger instead, or start the function with if pg_trigger_depth() > 1 then return null; end if;.
• Slow work inside a trigger. The trigger runs inside the user's transaction, so a slow trigger makes every write slow. For HTTP calls, use pg_net, which sends the request after commit; see Database webhooks.

Related

• Database functions
• Users and the auth schema
• Postgres Changes
• Broadcasting from the database