Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table final_table (fst_name,lst_name,state_code,row_nbr) as
select 'John', 'Doe', 13, 123 from dual;
1 rows affected

create table staging_table (fst_name,lst_name,state_code) as
select 'A', 'A', 13 from dual
union all
select 'B', 'B', 13 from dual
2 rows affected

declare
  start_with pls_integer;
begin
  select nvl(max(row_nbr), 0) + 1 into start_with from final_table;
  execute immediate 'create sequence final_seq start with ' || start_with;
end;
/
1 rows affected

create trigger final_trig
before insert on final_table
for each row
begin
  :new.row_nbr := final_seq.nextval;
end;
/
insert into final_table (fst_name, lst_name, state_code)
select st.*
from staging_table st
where st.state_code=13;
2 rows affected

select * from final_table;


FST_NAME
LST_NAME
STATE_CODE
ROW_NBR


John
Doe
13
123

A
A
13
124

B
B
13
125


insert into final_table (fst_name, lst_name, state_code, row_nbr)
values ('C', 'C', 13, 124);
1 rows affected

select * from final_table;


FST_NAME
LST_NAME
STATE_CODE
ROW_NBR


John
Doe
13
123

A
A
13
124

B
B
13
125

C
C
13
126