使用 view 的 instead of triggercreate table table1( id number(3), val number(30), pid number(3), primary key (id), foreign key (pid) references table1(id) );insert into table1 values( 0 , 100 , null); insert into table1 values( 1 , 200 , 0); insert into table1 values( 2 , 300 , 1); insert into table1 values( 3 , 400 , 2); insert into table1 values( 4 , 500 , 3);create or replace view v_table1 as select * from table1; create or replace trigger trg_v_table1 instead of update on v_table1 for each row begin for c in (select id,level from table1 start with id = :new.id connect by prior id = pid) loop update table1 set val = val + c.level * (:new.val - :old.val) where id = c.id; end loop; end; /update v_table1 set val = val + 42 where id = 0; select * from table1;
id number(3),
val number(30),
pid number(3),
primary key (id),
foreign key (pid) references table1(id)
);insert into table1 values( 0 , 100 , null);
insert into table1 values( 1 , 200 , 0);
insert into table1 values( 2 , 300 , 1);
insert into table1 values( 3 , 400 , 2);
insert into table1 values( 4 , 500 , 3);create or replace view v_table1 as select * from table1;
create or replace trigger trg_v_table1
instead of update on v_table1
for each row
begin
for c in (select id,level
from table1
start with id = :new.id
connect by prior id = pid)
loop
update table1 set val = val + c.level * (:new.val - :old.val)
where id = c.id;
end loop;
end;
/update v_table1 set val = val + 42 where id = 0;
select * from table1;