create or replace function notify_manager_of_new_student_connection() returns trigger as $$
    declare
        v_notification_source_id uuid;
        v_activity_types_id uuid;
        v_notification_id uuid;
    begin

        select id into v_activity_types_id
        from activity_types
        where activity_types.type = 'new_student_connection';

        insert into notification_sources(student_id)
        values (NEW.student_id)
        returning id into v_notification_source_id;

        insert into notifications(notification_source_id, activity_type_id)
        values (v_notification_source_id, v_activity_types_id)
        returning id into v_notification_id;

        insert into manager_notifications(notification_id, manager_id)
        values (v_notification_id, NEW.manager_id);
        return NEW;
    end;
&& language plpgsql volatile set search_path from current security definer;

create trigger trigger_manager_of_new_student_connection
after insert on manager_contacts
for each row execute procedure notify_manager_of_new_student_connection();