Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table person (
  id number primary key
, registration_number varchar2(9)
, primary_number varchar2(9)
, woman_act varchar2(1) check ( woman_act in ( null, 'X' ) )                
);
create table consolidated_numbers (
  secondary_number varchar2(9)
, person_id number references person( id )
);
create table code (
  valid_code varchar2(2) primary key
);
-- CHECK constraint added to allow only certain TYPE_IDs
create table history_transaction (
  reason varchar2(2) references code( valid_code ) -- valid REASONs enforced by FK constraint
, person_id number references person( id )
, type_id number check (
    type_id in (
      120, 140, 1420, 1440, 160, 180, 150, 1520, 1540, 1560  -- only allow these type_ids
    )
  )
, action_date date
);
-- INSERT your initial test data
begin
-- original test data
  insert into person (ID,registration_number,primary_number) values(132, '000000001', null);
  insert into person (ID,registration_number,primary_number) values (151, '000000002', '000000001');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000002', 132);
  insert into code (valid_code) values ('A1');
  insert into code (valid_code) values ('T1');
  insert into code (valid_code) values ('N2');
-- insert into history_transaction (reason,person_id,type_id,action_date)
--    values ('A1', 132, 1420, DATE '2019-01-01');
-- additional test data
  insert into person (ID,registration_number,primary_number) values (345, '000000345', '000000001');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000345', 132);
  insert into person (ID,registration_number,primary_number) values(1000, '000000777', null);
  insert into person (ID,registration_number,primary_number) values (2000, '000000778', '000000777');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000778', 1000);
  insert into person (ID,registration_number,primary_number) values(10000, '000007777', null);
  insert into person (ID,registration_number,primary_number) values (20000, '000007778', '000007777');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000007778', 10000);
  commit ;
end ;
/
1 rows affected

create or replace package pxpkg
is  
  function isparent( id_ number ) return boolean ;
  procedure setx_parentchildren( id_ number ) ;
  procedure setx_childsiblings( id_ number ) ;
end pxpkg ;
/
create or replace package body pxpkg
is
  function isparent( id_ number )
  return boolean
  is
    secondarynumbers pls_integer := 0 ;
  begin
    select count(*) into secondarynumbers
    from consolidated_numbers
    where person_id = id_ ;
    if secondarynumbers = 0 then
      return false ;
    else
      return true ;
    end if ;
  end isparent ;
--
  procedure setx_parentchildren ( id_ number )
  is
  begin
    update person
    set woman_act = 'X'
    where id in ( 
      select id from person where id = id_ -- parent id
      union
      select id from person 
      where primary_number = ( 
        select registration_number from person where id = id_ -- parent id
      )
    ) ;
  end setx_parentchildren ;
--
  procedure setx_childsiblings ( id_ number )
  is
  begin
    update person
    set woman_act = 'X'
    where id in ( 
      with PID as (
        select id, primary_number from person
        where id = id_                    -- current id
          and primary_number is not null  -- child ids only
      )
      select id from PID
      union
      select id 
      from person 
      where registration_number in ( select primary_number from PID )
         or primary_number in ( select primary_number from PID )
    ) ;
  end setx_childsiblings ;
end pxpkg ;
/
create or replace trigger pxtrigger
after insert or update on history_transaction
for each row
begin
  if pxpkg.isparent( :new.person_id ) then
    pxpkg.setx_parentchildren( :new.person_id )  ;
  else
    pxpkg.setx_childsiblings( :new.person_id )  ;
  end if ;
end pxtrigger ;
/
select * from person ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
null

151
000000002
000000001
null

345
000000345
000000001
null

1000
000000777
null
null

2000
000000778
000000777
null

10000
000007777
null
null

20000
000007778
000007777
null


select * from history_transaction ;
insert into history_transaction (reason, person_id, type_id, action_date)
  values ('A1', 132, 1420, DATE '2019-01-01');
1 rows affected

select * from history_transaction ;


REASON
PERSON_ID
TYPE_ID
ACTION_DATE


A1
132
1420
01-JAN-19


select * from person ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
X

151
000000002
000000001
X

345
000000345
000000001
X

1000
000000777
null
null

2000
000000778
000000777
null

10000
000007777
null
null

20000
000007778
000007777
null


update person set woman_act = null ;
7 rows affected

select * from person ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
null

151
000000002
000000001
null

345
000000345
000000001
null

1000
000000777
null
null

2000
000000778
000000777
null

10000
000007777
null
null

20000
000007778
000007777
null


update history_transaction
set reason = 'N2'
, person_id = 345
where person_id = 132 ;
1 rows affected

select * from history_transaction ;


REASON
PERSON_ID
TYPE_ID
ACTION_DATE


N2
345
1420
01-JAN-19


select * from person ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
X

151
000000002
000000001
X

345
000000345
000000001
X

1000
000000777
null
null

2000
000000778
000000777
null

10000
000007777
null
null

20000
000007778
000007777
null
ID	REGISTRATION_NUMBER	PRIMARY_NUMBER	WOMAN_ACT
132	000000001	null	null
151	000000002	000000001	null
345	000000345	000000001	null
1000	000000777	null	null
2000	000000778	000000777	null
10000	000007777	null	null
20000	000007778	000007777	null