How to Use Triggers in Supabase

1-- Simple trigger function: auto-set updated_at on UPDATE
2create or replace function public.set_updated_at()
3returns trigger
4language plpgsql
5as $$
6begin
7  new.updated_at = now();
8  return new;
9end;
10$$;

1-- Create a table with timestamps
2create table public.posts (
3  id uuid primary key default gen_random_uuid(),
4  user_id uuid references auth.users(id) on delete cascade,
5  title text not null,
6  content text,
7  created_at timestamptz default now(),
8  updated_at timestamptz default now()
9);
10
11-- Enable RLS on the table
12alter table public.posts enable row level security;
13
14-- Attach the trigger: auto-update updated_at on every UPDATE
15create trigger set_posts_updated_at
16  before update on public.posts
17  for each row
18  execute function public.set_updated_at();
19
20-- RLS policy: users can manage their own posts
21create policy "Users manage own posts" on public.posts
22  for all to authenticated
23  using (auth.uid() = user_id)
24  with check (auth.uid() = user_id);

1-- Create the profiles table
2create table public.profiles (
3  id uuid primary key references auth.users(id) on delete cascade,
4  email text,
5  full_name text,
6  avatar_url text,
7  created_at timestamptz default now()
8);
9
10alter table public.profiles enable row level security;
11
12create policy "Public profiles are viewable by everyone"
13  on public.profiles for select
14  to anon, authenticated
15  using (true);
16
17create policy "Users can update own profile"
18  on public.profiles for update
19  to authenticated
20  using (auth.uid() = id)
21  with check (auth.uid() = id);
22
23-- Create the trigger function
24create or replace function public.handle_new_user()
25returns trigger
26language plpgsql
27security definer set search_path = ''
28as $$
29begin
30  insert into public.profiles (id, email, full_name, avatar_url)
31  values (
32    new.id,
33    new.email,
34    new.raw_user_meta_data ->> 'full_name',
35    new.raw_user_meta_data ->> 'avatar_url'
36  );
37  return new;
38end;
39$$;
40
41-- Attach to auth.users
42create trigger on_auth_user_created
43  after insert on auth.users
44  for each row
45  execute function public.handle_new_user();

1-- Validate that post titles are not empty and not too long
2create or replace function public.validate_post()
3returns trigger
4language plpgsql
5as $$
6begin
7  if length(trim(new.title)) = 0 then
8    raise exception 'Post title cannot be empty';
9  end if;
10
11  if length(new.title) > 200 then
12    raise exception 'Post title cannot exceed 200 characters';
13  end if;
14
15  -- Sanitize: trim whitespace
16  new.title = trim(new.title);
17  return new;
18end;
19$$;
20
21create trigger validate_post_before_insert
22  before insert or update on public.posts
23  for each row
24  execute function public.validate_post();

1-- Add debug logging to a trigger function
2create or replace function public.handle_new_user()
3returns trigger
4language plpgsql
5security definer set search_path = ''
6as $$
7begin
8  raise notice 'Creating profile for user: %, email: %', new.id, new.email;
9
10  insert into public.profiles (id, email, full_name, avatar_url)
11  values (
12    new.id,
13    new.email,
14    new.raw_user_meta_data ->> 'full_name',
15    new.raw_user_meta_data ->> 'avatar_url'
16  );
17
18  raise notice 'Profile created successfully for user: %', new.id;
19  return new;
20end;
21$$;