SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table reference (
  id int identity(10,1) primary key,
  type varchar(255) not null
);
create table "user" (
  id int identity(20,1) primary key,
  name varchar(255) not null,
  reference_id int references reference(id)
);
create trigger on_insert_add_reference on "user"
  after insert
as
  declare @ref_ids table(id int, user_id int)
  set nocount on
begin
  merge reference 
  using inserted as inserted_users
  on (1 = 0)
  when not matched then
      insert ("type") values('external')
  output inserted.id, inserted_users.id into @ref_ids;
  
  update "user"
  set reference_id = [@ref_ids].id
  from "user"
  join inserted on inserted.id = "user".id
  join @ref_ids on [@ref_ids].user_id = "user".id;
end
insert into "user" (name) values ('FNG'), ('Unpaid Intern');
2 rows affected

select * from "user";


id
name
reference_id


20
FNG
11

21
Unpaid Intern
10


select * from reference;


id
type


10
external

11
external