contact_table_triggers.sql

contact_table_triggers.sql

create function customer_search_email_insert()
   returns trigger
   security definer
   language plpgsql
as $$
begin
   insert into customer_search(customer_id, contact_method, is_stale, term, contact_id)
   values(new.customer_id, 'Email', true, new.email_address, new.id);
   return new;
end;
$$;
create trigger lazy_email_insert after insert on email
for each row execute procedure customer_search_email_insert();

create function customer_search_phone_insert()
   returns trigger
   security definer
   language plpgsql
as $$
begin
   insert into customer_search(customer_id, contact_method, is_stale, term, contact_id)
   -- term is computed as a combination of the international format without the leading '+' and the national format
   values (new.customer_id, 'Phone', true, trim(leading '+' from new.phone_number) || ' ' || new.national_format, new.id);
   return new;
end;
$$;
create trigger lazy_phone_insert after insert on phone
for each row execute procedure customer_search_phone_insert();

create function customer_search_email_update()
   returns trigger
   language plpgsql
   security definer
as $$
begin
   if new.deleted_at is null then
       update customer_search
       set is_stale=true,
           term=new.email_address
       where customer_id=new.customer_id
             and contact_id=new.id
             and is_stale <> true;
   else
       delete from customer_search
       where customer_id=new.customer_id and
             contact_id=new.id;
   end if;
   return new;
end;
$$;
create trigger lazy_email_update after update on email
for each row execute procedure customer_search_email_update();

create function customer_search_phone_update()
   returns trigger
   security definer
   language plpgsql
as $$
begin
   if new.deleted_at is null then
       update customer_search
       set is_stale=true,
           term=trim(leading '+' from new.phone_number) || ' ' || new.national_format
       where customer_id=new.customer_id
             and contact_id=new.id
             and is_stale<>true;
   else
       delete from customer_search
       where customer_id=new.customer_id and
             contact_id=new.id;
   end if;
   return new;
end;
$$;
create trigger lazy_phone_update after update on phone
for each row execute procedure customer_search_phone_update();

create function customer_search_contact_delete()
   returns trigger
   security definer
   language plpgsql
as $$
begin
   delete from customer_search
   where customer_id=old.customer_id and
         contact_id=old.id;
   return old;
end;
$$;
create trigger lazy_phone_delete after delete on phone
for each row execute procedure customer_search_contact_delete();
create trigger lazy_email_delete after delete on email
for each row execute procedure customer_search_contact_delete();