# How to Use Triggers in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Supabase (all plans), PostgreSQL 14+
- Last updated: March 2026

## TL;DR

To use triggers in Supabase, create a PL/pgSQL function that returns trigger, then attach it to a table with CREATE TRIGGER specifying BEFORE or AFTER and the event type (INSERT, UPDATE, DELETE). The most common pattern is auto-creating a user profile when a new auth.users row is inserted. Triggers run inside the same transaction as the original operation, so they are guaranteed to execute or roll back together.

## Creating and Using Database Triggers in Supabase

PostgreSQL triggers are functions that execute automatically when a specified event (INSERT, UPDATE, DELETE) occurs on a table. In Supabase, triggers are commonly used to auto-create user profiles on signup, update timestamps, validate data before writes, and sync data between tables. This tutorial covers creating trigger functions, attaching them to tables, understanding BEFORE vs AFTER triggers, and debugging common trigger issues.

## Before you start

- A Supabase project (free tier or above)
- Access to the SQL Editor in Supabase Dashboard
- Basic knowledge of SQL and PL/pgSQL
- A table to attach the trigger to (we will create one)

## Step-by-step guide

### 1. Create a trigger function

A trigger function is a regular PL/pgSQL function that returns the trigger type. Inside the function, you have access to special variables: NEW (the row being inserted or updated), OLD (the row before update or being deleted), and TG_OP (the operation type: INSERT, UPDATE, or DELETE). The function must return NEW for INSERT/UPDATE triggers or OLD for DELETE triggers. Returning NULL from a BEFORE trigger cancels the operation.

```
-- Simple trigger function: auto-set updated_at on UPDATE
create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;
```

**Expected result:** The trigger function is created and ready to be attached to a table.

### 2. Attach the trigger to a table

Use CREATE TRIGGER to attach your function to a table. Specify BEFORE or AFTER to control when the function runs relative to the operation. BEFORE triggers can modify the row (e.g., set default values) or cancel the operation (by returning NULL). AFTER triggers run after the row is written and are used for side effects like logging or syncing data to other tables. FOR EACH ROW means the function runs once per affected row.

```
-- Create a table with timestamps
create table public.posts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  title text not null,
  content text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Enable RLS on the table
alter table public.posts enable row level security;

-- Attach the trigger: auto-update updated_at on every UPDATE
create trigger set_posts_updated_at
  before update on public.posts
  for each row
  execute function public.set_updated_at();

-- RLS policy: users can manage their own posts
create policy "Users manage own posts" on public.posts
  for all to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);
```

**Expected result:** Every time a row in the posts table is updated, the updated_at column is automatically set to the current timestamp.

### 3. Implement the handle_new_user trigger pattern

The most common Supabase trigger pattern is auto-creating a profile row when a new user signs up. This trigger fires AFTER INSERT on auth.users and inserts a row into your public profiles table. Use security definer so the function can write to the profiles table regardless of the calling user's permissions. Always set search_path to empty string when using security definer.

```
-- Create the profiles table
create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  full_name text,
  avatar_url text,
  created_at timestamptz default now()
);

alter table public.profiles enable row level security;

create policy "Public profiles are viewable by everyone"
  on public.profiles for select
  to anon, authenticated
  using (true);

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

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

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

**Expected result:** Every new user signup automatically creates a corresponding row in the profiles table with their email and metadata.

### 4. Create a validation trigger with BEFORE INSERT

BEFORE triggers can validate data and reject invalid rows by raising an exception. This is useful for enforcing business rules that cannot be expressed with CHECK constraints or RLS policies. When a BEFORE trigger raises an exception, the entire operation is rolled back.

```
-- Validate that post titles are not empty and not too long
create or replace function public.validate_post()
returns trigger
language plpgsql
as $$
begin
  if length(trim(new.title)) = 0 then
    raise exception 'Post title cannot be empty';
  end if;

  if length(new.title) > 200 then
    raise exception 'Post title cannot exceed 200 characters';
  end if;

  -- Sanitize: trim whitespace
  new.title = trim(new.title);
  return new;
end;
$$;

create trigger validate_post_before_insert
  before insert or update on public.posts
  for each row
  execute function public.validate_post();
```

**Expected result:** Attempting to insert a post with an empty or overly long title returns an error, and the row is not inserted.

### 5. Debug trigger failures using logs

When a trigger fails, the error may not be immediately visible. In the Supabase Dashboard, go to Logs in the left sidebar and check the Postgres logs for trigger-related errors. Common issues include: the trigger function references a column that does not exist, the profiles table has a NOT NULL constraint that is violated, or the security definer function does not have permission to write to the target table. Use RAISE NOTICE for debugging output.

```
-- Add debug logging to a trigger function
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  raise notice 'Creating profile for user: %, email: %', new.id, new.email;

  insert into public.profiles (id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data ->> 'full_name',
    new.raw_user_meta_data ->> 'avatar_url'
  );

  raise notice 'Profile created successfully for user: %', new.id;
  return new;
end;
$$;
```

**Expected result:** Trigger execution details appear in the Postgres logs, making it easy to identify where failures occur.

## Complete code example

File: `triggers-setup.sql`

```sql
-- ============================================
-- Supabase Triggers Setup
-- ============================================

-- 1. Reusable trigger function: auto-set updated_at
create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- 2. Profiles table linked to auth.users
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  full_name text,
  avatar_url text,
  created_at timestamptz default now()
);

alter table public.profiles enable row level security;

create policy "Public profiles viewable" on public.profiles
  for select to anon, authenticated using (true);
create policy "Users update own profile" on public.profiles
  for update to authenticated
  using (auth.uid() = id) with check (auth.uid() = id);

-- 3. Auto-create profile on user signup
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data ->> 'full_name',
    new.raw_user_meta_data ->> 'avatar_url'
  );
  return new;
end;
$$;

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

-- 4. Posts table with auto-updating timestamp
create table if not exists public.posts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  title text not null,
  content text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

alter table public.posts enable row level security;

create policy "Users manage own posts" on public.posts
  for all to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create trigger set_posts_updated_at
  before update on public.posts
  for each row execute function public.set_updated_at();
```

## Common mistakes

- **Forgetting to set search_path = '' when using security definer in trigger functions** — undefined Fix: Always add 'security definer set search_path = ''' to functions that use security definer. Without it, a malicious user could potentially exploit the search path to call unintended functions.
- **Using a BEFORE trigger to insert into another table, which fails because the source row does not exist yet** — undefined Fix: Use AFTER triggers for side effects that reference the newly inserted row. BEFORE triggers should only modify the current row (via NEW) or validate data.
- **Returning NULL from a BEFORE INSERT trigger, which silently cancels the insert** — undefined Fix: Always return NEW from BEFORE INSERT and BEFORE UPDATE triggers unless you intentionally want to cancel the operation. Return OLD from BEFORE DELETE triggers.
- **Creating recursive triggers that call each other in an infinite loop** — undefined Fix: If a trigger function modifies the same table it is attached to, add a condition to prevent recursion, or use pg_trigger_depth() to detect re-entry: IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF;

## Best practices

- Use BEFORE triggers for data validation and modification, AFTER triggers for side effects and logging
- Always use security definer with set search_path = '' when the trigger function needs to access restricted schemas like auth
- Return NEW from INSERT/UPDATE trigger functions and OLD from DELETE trigger functions
- Keep trigger functions simple and fast — they run inside the transaction and block the original query
- Create reusable trigger functions (like set_updated_at) that can be attached to multiple tables
- Add RLS policies on any tables that trigger functions write to, even if the trigger uses security definer
- Use RAISE NOTICE for temporary debugging output, visible in the Dashboard Postgres logs
- Document your triggers with comments explaining what they do and why

## Frequently asked questions

### What is the difference between BEFORE and AFTER triggers?

BEFORE triggers run before the row is written to the table. They can modify the row (change NEW values) or cancel the operation (return NULL). AFTER triggers run after the row is committed and are used for side effects like inserting into other tables or sending notifications.

### Can I have multiple triggers on the same table?

Yes, you can have multiple triggers on the same table and same event. They execute in alphabetical order by trigger name. Use naming conventions to control execution order if needed.

### How do I remove a trigger?

Use DROP TRIGGER trigger_name ON table_name. For example: DROP TRIGGER set_posts_updated_at ON public.posts; This removes the trigger but keeps the function.

### Can triggers access the authenticated user's ID?

Yes, use auth.uid() inside the trigger function to get the current user's UUID. This works because the Supabase client sends the JWT with every request, and PostgreSQL makes it available via the auth.uid() helper.

### Why does my handle_new_user trigger fail silently?

Check the Postgres logs in the Dashboard under Logs. Common causes: the profiles table has a NOT NULL column that is not being set, the trigger function has a syntax error, or the function does not use security definer and lacks permission to insert into the profiles table.

### Do triggers work with RLS?

Triggers run in the context of the calling user unless the function uses security definer. With security definer, the function runs as the function owner (usually postgres) and bypasses RLS. This is why handle_new_user uses security definer — the signing-up user does not yet have permission to insert into profiles.

### Can RapidDev help set up database triggers for my Supabase project?

Yes, RapidDev can design and implement trigger-based automation for your Supabase database, including user profile creation, data validation, audit logging, and cross-table synchronization.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-use-triggers-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-use-triggers-in-supabase