Dependent Database Triggers & Functions

I have a database trigger that creates a user_shop for a user after a shop is created. Via the following trigger/function

create trigger add_shop_user_from_shop
  after insert on public.shops
  for each row execute procedure public.add_user_shop_from_shop();

create function public.add_user_shop_from_shop() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.user_shops(user_id, shop_id, role)
  values (auth.uid(), new.id, 'ADMIN');

  return new;
end;
$$;

create trigger add_shop_user_from_shop
  after insert on public.shops
  for each row execute procedure public.add_user_shop_from_shop();

create function public.add_user_shop_from_shop() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.user_shops(user_id, shop_id, role)
  values (auth.uid(), new.id, 'ADMIN');

  return new;
end;
$$;

create trigger add_shop_user_from_shop
  after insert on public.shops
  for each row execute procedure public.add_user_shop_from_shop();

create function public.add_user_shop_from_shop() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.user_shops(user_id, shop_id, role)
  values (auth.uid(), new.id, 'ADMIN');

  return new;
end;
$$;

create trigger add_shop_user_from_shop
  after insert on public.shops
  for each row execute procedure public.add_user_shop_from_shop();

create function public.add_user_shop_from_shop() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.user_shops(user_id, shop_id, role)
  values (auth.uid(), new.id, 'ADMIN');

  return new;
end;
$$;

Which all works fine and well.

I've decided that I want to create a shop for a user when a user record is created. However, I can not able to get that trigger/function to work as the prior one is already migrated into the database. I am trying

create trigger create_shop_from_user
  after insert on public.users
  for each row execute procedure public.create_shop_from_user();

create function public.create_shop_from_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.shops DEFAULT VALUES;

  return new;
end;
$$;

create trigger create_shop_from_user
  after insert on public.users
  for each row execute procedure public.create_shop_from_user();

create function public.create_shop_from_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.shops DEFAULT VALUES;

  return new;
end;
$$;

create trigger create_shop_from_user
  after insert on public.users
  for each row execute procedure public.create_shop_from_user();

create function public.create_shop_from_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.shops DEFAULT VALUES;

  return new;
end;
$$;

create trigger create_shop_from_user
  after insert on public.users
  for each row execute procedure public.create_shop_from_user();

create function public.create_shop_from_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.shops DEFAULT VALUES;

  return new;
end;
$$;

But I am getting an error saving the user and its due to the association of the user_shop to the user. How do I create a shop from an inserted user in the new function while allowing the existing function that creates the user_shop association row to still run? Any help is appreciated!

Dependent Database Triggers & Functions

Similar Threads

Similar Threads

Similar Threads