Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table app_users
(my_application_user varchar2(50)
,client_counter number default 0  not null 
);
create table app_clients
(my_application_user varchar2(50)
,client_id varchar2(100)
,user_client_number number 
);
begin 
insert into app_users (my_application_user) values ('Company X');
insert into app_users (my_application_user) values ('Company Y');
insert into app_users (my_application_user) values ('Company Z');
end;
/
1 rows affected

create or replace trigger app_clients_ins
before insert 
on app_clients
for each row
declare
begin
  update app_users
  set    client_counter = client_counter + 1
  where  my_application_user = :new.my_application_user
  return client_counter into :new.user_client_number;
  
end;
/
begin
insert into app_clients (my_application_user, client_id) values ('Company X','Client 201');
insert into app_clients (my_application_user, client_id) values ('Company Y','Client 202');
insert into app_clients (my_application_user, client_id) values ('Company Z','Client 203');
insert into app_clients (my_application_user, client_id) values ('Company Y','Client 204');
end;
/
1 rows affected

select * from app_clients order by 1,3;


MY_APPLICATION_USER
CLIENT_ID
USER_CLIENT_NUMBER


Company X
Client 201
1

Company Y
Client 202
1

Company Y
Client 204
2

Company Z
Client 203
1


insert into app_clients (my_application_user, client_id)
select 'Company Z','Client '||(204+rownum)
from dual connect by rownum <=5;
5 rows affected

select * from app_clients order by 1,3;


MY_APPLICATION_USER
CLIENT_ID
USER_CLIENT_NUMBER


Company X
Client 201
1

Company Y
Client 202
1

Company Y
Client 204
2

Company Z
Client 203
1

Company Z
Client 205
2

Company Z
Client 206
3

Company Z
Client 207
4

Company Z
Client 208
5

Company Z
Client 209
6


select * from app_users order by 1;


MY_APPLICATION_USER
CLIENT_COUNTER


Company X
1

Company Y
2

Company Z
6

MY_APPLICATION_USER	CLIENT_ID	USER_CLIENT_NUMBER
Company X	Client 201	1
Company Y	Client 202	1
Company Y	Client 204	2
Company Z	Client 203	1