# Triggers

> Run Postgres functions automatically when rows change in a SupaHost project: updated_at timestamps, creating profiles on sign-up, audit logs, and avoiding common trigger mistakes.

Source: https://supahost.app/docs/database/triggers · Updated 2026-11-11

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.

| Choice | Options | Meaning |
|---|---|---|
| Timing | `before`, `after` | `before` can change or cancel the row; `after` sees the final row |
| Event | `insert`, `update`, `delete` (combine with `or`), `update of col` | What fires it |
| Level | `for each row`, `for each statement` | Once per row, or once per SQL statement |

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.

```sql
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`.

```sql
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:

```ts
await supabase.auth.signUp({
  email: 'cook@example.com',
  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](/kb/database-error-saving-new-user).

## Example: an audit log

Record who changed what on invoices:

```sql
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

```sql
-- 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](/kb/new-is-null-in-trigger).
- **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](/docs/database/webhooks).

## Related

- [Database functions](/docs/database/functions)
- [Users and the auth schema](/docs/auth/users)
- [Postgres Changes](/docs/realtime/postgres-changes)
- [Broadcasting from the database](/kb/realtime-broadcast-from-database)