S
Supabase•3y ago
Zeph

Trigger Function

I currently have this trigger and function to add a new row to my public users table whenever a new user signs up. How do I also trigger a function that adds a row in the folder table with the name set to 'default' and the userId set to the new user that was just created? My folder table has the following columns: id, name, userId Thank you! 
create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user()
create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user()
13 Replies
silentworks
silentworks•3y ago
I'm not sure what you're asking here? are you asking how to create another trigger function?
zackderose
zackderose•3y ago
Hey @Zeph, I believe it'd be very similar: 
create or replace function public.create_folder_for_new_user() 
returns trigger as $create_folder_for_new_user$
begin
  insert into public.folder (name, userId)
  values ('default', new.id);
  return new;
end;
$create_folder_for_new_user$ language plpgsql;

drop trigger if exists create_folder_for_new_user on auth.users;
create trigger create_folder_for_new_user
  after insert on auth.users
  for each row execute procedure public.create_folder_for_new_user()
create or replace function public.create_folder_for_new_user() 
returns trigger as $create_folder_for_new_user$
begin
  insert into public.folder (name, userId)
  values ('default', new.id);
  return new;
end;
$create_folder_for_new_user$ language plpgsql;

drop trigger if exists create_folder_for_new_user on auth.users;
create trigger create_folder_for_new_user
  after insert on auth.users
  for each row execute procedure public.create_folder_for_new_user()
Couple things to note: - single quotes here used for a string literal - double quotes are used only to escape identifiers (like table name or row names) - your public folder should have a default way to generate an id, so you shouldn't need to provide one on inserts
Zeph
ZephOP•3y ago
@zackderose Thanks so much! I tried it out but I get database error saving new user. This is an issue I've been facing whenever I try to create any other trigger other than the one in my original post. Any idea what could be the cause of it?
zackderose
zackderose•3y ago
interesting - is this error when you do this in the sql editor? Or when you insert?
Zeph
ZephOP•3y ago
When I try to sign up a new user.
garyaustin
garyaustin•3y ago
You should not be using capital letters in Postgres for names of tables or columns unless forced to by a 3rd party application. You have to remember to always put them in double quotes "userId" when you use them in SQL.
Zeph
ZephOP•3y ago
@garyaustin ahh.. okay let me give that a shot(: Thank you!
zackderose
zackderose•3y ago
ooh good to know Yes I'm finding i didn't know sql as well as I thought I did lol
garyaustin
garyaustin•3y ago
https://discord.com/channels/839993398554656828/885237287280070708/988097402059780098 Also when you get a 500 error, you can usually look at the Postgres logs right after and it will tell you what was wrong.
zackderose
zackderose•3y ago
is the idea to tail the machine remotely @garyaustin or is there some way to see the logs via the ui?
Zeph
ZephOP•3y ago
@zackderose There is a postgres logs in the UI(:
zackderose
zackderose•3y ago
oh nice - looks like it's not in local dev - but the db logs are accessible via docker here
Zeph
ZephOP•3y ago
@garyaustin I checked the logs and I'm got the following: 
Log Event Message
permission denied for schema public
Log Event Message
permission denied for schema public
Any idea what could be causing this? My RLS is disabled. I can confirm that it is the trigger that create_folder_for_new_user trigger that is causing the issue but I'm not sure why. Without this trigger, the trigger that calls the handle_new_user function works fine.

Did you find this page helpful?