Compound trigger to insert into another table after insert or update into the main table

I’m working on a compound trigger and I’m having difficulties figuring out how to make it work. The trigger is on the person table. There is a column woman_act. If a new row is inserted and that column is not null than we need to insert that row into the v_changes table or if that column is updated from null to ‘X’ than insert that row into v_changes table. Going with COMPOUND trigger becuase I was getting mutating table and that was happening becuase I’m inserting from select.

Here is the logic

--Insert with person.woman_act == ‘ ’ do nothing
--Insert with person.woman_act == ‘X’ insert with is_vawa = 1/true
--Update with person.woman_act changed from ‘ ‘ to ‘X’ insert with is_woman_act = 1/true

create table person (
  id number primary key,
  reg_number varchar2(9),
  last_name charchar2(255),
  first_name charchar2(255),
  dob date,
  birth_c_id number(38,0),
  cit_country_id number(38,0),
  poe_id number(38,0),
  woman_act varchar2(1) check (woman_act in (null, 'X')) 
);

create table v_changes (
 id number primary key,
 person_id number not null,
 reg_number varchar2(9),
 last_name charchar2(255),
 first_name charchar2(255),
 dob date,
 b_country varchar2(255),
 cit_country varchar2(255),
 poe varchar2(30),
 is_woman_act number(1,0)
);

create table country_code (
 id number primary key,
 valid_code varchar2(255)
);

create table loc_code (
 id number primary key,
 valid_code varchar2(255)
);

Here is my trigger. Any ideas and suggestions much appreciated.

CREATE OR REPLACE TRIGGER v_changes_trg 
    FOR INSERT OR UPDATE OF woman_act ON person 
    COMPOUND TRIGGER
    
   
    TYPE t_vawa_type IS TABLE OF person.woman_act%TYPE;
    t_vawa   t_vawa_type := t_vawa_type();  
    
    TYPE t_v_id IS TABLE OF person.id%TYPE;
    v_ids t_v_id :=t_v_id();

    BEFORE STATEMENT IS 
        t_vawas := t_vawa_type(); 
        v_ids :=t_v_id(); 
    END BEFORE STATEMENT;

    BEFORE EACH ROW IS
        t_vawa_id   person.id%ROWTYPE;  
        
    BEGIN
        
        IF INSERTING AND :NEW.woman_act IS NOT NULL THEN
            v_id.extend;
            v_ids(v_ids.last) := :NEW.v_ids;
        END IF;
           
        
        IF UPDATING AND (:NEW.woman_act IS NOT NULL AND :OLD.woman_act IS NULL) OR (:OLD.woman_act <> :NEW.woman_act) THEN
            v_id.extend;
            v_ids(v_ids.last) := :OLD.v_ids;
        END IF;
     
    END BEFORE EACH ROW; 

    
    AFTER STATEMENT IS  
        
    BEGIN
        
        FOR i IN 1..v_ids.LAST LOOP
                INSERT INTO v_changes(
                    id,
                    person_id, 
                    reg_number, 
                    last_name, 
                    first_name, 
                    dob, 
                    b_country, 
                    cit_country, 
                    poe, 
                    is_woman_act
                )
            
                SELECT 
                    v_changes_seq.nextval,
                    p.id, --personId
                    p.reg_number, 
                    p.last_name, 
                    p.first_name, 
                    p.dob, 
                    cc1.valid_code, 
                    cc2.valid_code, 
                    poe.valid_code,
                    (case woman_act when 'X' then 1 else 0 end)
            
                FROM person p
                    LEFT JOIN country_code cc1 ON p.birth_c_id = cc1.id
                    LEFT JOIN country_code cc2 ON p.cit_country_id = cc2.id
                    LEFT JOIN loc_code poe ON p.poe_id = poe.id
            END LOOP;
    END AFTER STATEMENT;   
END vawa_changes_trg;

Answer

There doesn’t appear to be a need to use a compound trigger here because there is no need to query the person table. Just use a row-level trigger and reference the :new and :old pseudorecords in your logic

create or replace trigger trg_biur_person
  before insert or update on person
  for each row
begin
  IF :new.woman_act = 'X'
  THEN
    INSERT INTO v_changes(
                    id,
                    person_id, 
                    reg_number, 
                    last_name, 
                    first_name, 
                    dob, 
                    b_country, 
                    cit_country, 
                    poe, 
                    is_woman_act
                )
     SELECT  
                    v_changes_seq.nextval,
                    :new.id, --personId
                    :new.reg_number, 
                    :new.last_name, 
                    :new.first_name, 
                    :new.dob, 
                    (select cc1.valid_code
                       from country_code cc1
                      where cc1.id = :new.birth_c_id), 
                    (select cc2.valid_code
                       from country_code cc2
                      where cc2.id = :new.cit_country_id), 
                    (select poe.valid_code
                       from loc_code poe
                      where poe.poe_id = :new.birth_c_id), 
                    (case :new.woman_act when 'X' then 1 else 0 end)
    from dual;
  end if;
end;

Assuming you’re just learning PL/SQL, it may be easier to create some local variables and use those in your insert statement, i.e.

create or replace trigger trg_biur_person
  before insert or update on person
  for each row
declare
  l_b_country v_changes.b_country%type;
begin
  begin
    select cc1.valid_code
      into l_b_country
      from country_code cc1
     where cc1.id = :new.birth_c_id;
  exception
    -- The fact that you're trying to do a `left join` makes me believe
    -- that you want to allow someone to insert a row with a `birth_c_id`
    -- that doesn't exist in the `country_code` table.  That doesn't make
    -- sense to me but that's what I'm going with.  If that isn't a valid 
    -- use case, you can omit the exception handler
    when no_data_found
    then
      l_b_country := null;
  end;

  ...

  insert into v_changes ...
    values( ..., l_b_country, ... );
end;