ORACLE在触发器中以1对多表关系更新记录

请勿为此使用触发器。您已将大多数条件编码到（触发器的）嵌套IF中，可以通过外键约束和检查约束来完成。另外，您不需要在任何地方存储WOMAN_ACT的“ X”，因为它是“派生值”，即在查询数据时可以获取或生成它。也许以下示例（基于原始表和数据）将帮助您找到解决方案。请阅读代码中的注释。

DDL代码

create table person (
  id number primary key
, registration_number varchar2(9) unique
, primary_number varchar2(9)
-- , women_act varchar2(1)   <- not needed!
); 
  
create table consolidated_numbers (
  secondary_number varchar2(9) references person( registration_number )
, 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 REASONSs 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
  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');
  commit ;
end;
/

以下VIEW将从HISTORY_TRANSACTION表中提取person_id，在每个表中添加和“ X”，并从CONSOLIDATED_NUMBERS中提取与这些ID“关联”（或映射到）的所有人员，并添加一个他们的ID为“ X”。（旁注：您的PERSON表似乎包含一个递归关系，因此可以编写一个递归查询。但是，您将需要对CONSOLIDATED_NUMBERS表进行建模，因此我们将在此处使用JOIN。）

视图

create or replace view personx
as
with PID as (
  select distinct person_id
  from history_transaction
)
select person_id, 'X' as woman_act  -- [Q1] all person_ids from history_transaction
from PID
union
select P.id, 'X' as woman_act       -- [Q2] all person_ids associated with ids from Q1
from person P
  join consolidated_numbers C
    on P.registration_number = C.secondary_number
    and C.person_id in (
      select person_id from PID
    )
;

-- with your initial test data, we get:
select * from personx ;
+---------+---------+
|PERSON_ID|WOMAN_ACT|
+---------+---------+
|132      |X        |
|151      |X        |
+---------+---------+

现在，让我们删除/添加一些数据，并运行一些测试（另请参见：DBfiddle）：

-- test 1
delete from history_transaction ;
select * from personx ;
-- result: no rows selected -> OK

-- test 2
insert into history_transaction (reason,person_id,type_id,action_date) 
  values ('A1', 132, 1420, DATE '2019-01-01');
  
select * from personx ;
+---------+---------+
|PERSON_ID|WOMAN_ACT|
+---------+---------+
|132      |X        |
|151      |X        |
+---------+---------+

-- test 3: add more associations
begin   
-- new: person 345 associated with person 132
  insert into person (ID,registration_number,primary_number) values (345, '000000345', '000000001');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000345', 132);
  commit ;
end ;
/

select * from personx ;
+---------+---------+
|PERSON_ID|WOMAN_ACT|
+---------+---------+
|132      |X        |
|151      |X        |
|345      |X        |
+---------+---------+

在我们进入更多细节之前的另一项测试：

-- test 4
-- add more associations 
-- no entry in history_transactions for person(id) 1000        
begin   
  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);
  commit ;
end ;
/   

-- output must be the same as before -> result OK
select * from personx ;
+---------+---------+
|PERSON_ID|WOMAN_ACT|
+---------+---------+
|132      |X        |
|151      |X        |
|345      |X        |
+---------+---------+

JOIN视图person表

-- test 5
-- add an entry from person 1000 into the history_transaction table
insert into history_transaction (reason,person_id,type_id,action_date) 
    values ('N2', 1000, 1420, sysdate);  

select * from personx ;
+---------+---------+
|PERSON_ID|WOMAN_ACT|
+---------+---------+
|132      |X        |
|151      |X        |
|345      |X        |
|1000     |X        |
|2000     |X        |
+---------+---------+

-- test 5: show more details
select P.id, P.registration_number, P.primary_number, PX.woman_act
from personx PX right join person P on PX.person_id = P.id ;

+----+-------------------+--------------+---------+
|ID  |REGISTRATION_NUMBER|PRIMARY_NUMBER|WOMAN_ACT|
+----+-------------------+--------------+---------+
|132 |000000001          |NULL          |X        |
|151 |000000002          |000000001     |X        |
|345 |000000345          |000000001     |X        |
|1000|000000777          |NULL          |X        |
|2000|000000778          |000000777     |X        |
+----+-------------------+--------------+---------+

对于在HISTORY_TRANSACTION表中没有对应行的PERSON_ID，需要外部联接

-- test 6
-- add more associations
-- no entry in history_transactions for person(id) 10000!
begin
  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 ;
/

-- after TEST 6 data have been inserted:
select P.id, P.registration_number, P.primary_number, PX.woman_act
from personx PX right join person P on PX.person_id = P.id ;

+-----+-------------------+--------------+---------+
|ID   |REGISTRATION_NUMBER|PRIMARY_NUMBER|WOMAN_ACT|
+-----+-------------------+--------------+---------+
|132  |000000001          |NULL          |X        |
|151  |000000002          |000000001     |X        |
|345  |000000345          |000000001     |X        |
|1000 |000000777          |NULL          |X        |
|2000 |000000778          |000000777     |X        |
|20000|000007778          |000007777     |NULL     |
|10000|000007777          |NULL          |NULL     |
+-----+-------------------+--------------+---------+

编辑

如果-如您的注释中所述-您必须在WOMAN_ACT列中存储一个值（尽管它显然是“派生的值”），则可以编写一个包含所有必需DML操作过程的程序包-仍不使用触发器。但是，在不了解全文的情况下，很难决定这是否是最好的方法。以下示例使用一个小型程序包，其中包含用于设置PERSON表的WOMAN_ACT值的过程以及在INSERT / UPDATEs之后触发的触发器（表：HISTORY_TRANSACTIONS）。DBfiddle在这里。

PERSON表

create table person (
  id number primary key
, registration_number varchar2(9) unique
, primary_number varchar2(9)
, woman_act varchar2(1) check ( woman_act in ( null, 'X' ) )
);
-- all other tables: same as before

包

create or replace package pxpkg
is
  -- find out whether a certain id (table: PERSON) is a "parent" or a "child"
  function isparent( id_ number ) return boolean ;
  -- set 'X' values: id_ is a "parent"
  procedure setx_parentchildren( id_ number ) ;
  -- set 'X' values: id_ is a "child" 
  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 ;
/

测试：请参阅DBfiddle