情景:
一个存在父子关系的表结构 要求插入,更新时 父节点 的 统计出子节点对应字段的和
表结构:
create table TREE_PARENT_CHILD
(
id VARCHAR2(50) not null,
parentid VARCHAR2(50) not null,
need_sum NUMBER
);
(
id VARCHAR2(50) not null,
parentid VARCHAR2(50) not null,
need_sum NUMBER
);
触发器:
create or replace trigger trgger_parent_child_sum
after insert or update on tree_parent_child
for each row
declare
-- 自发事物
PRAGMA AUTONOMOUS_TRANSACTION;
begin
if inserting then
dbms_output.put_line('--inserting---');
update tree_parent_child t set t.need_sum = (select nvl(sum(need_sum),0)+:new.need_sum from tree_parent_child where parentid= :new.parentid )
where id= :new.parentid;
commit;
elsif updating then
dbms_output.put_line('----updating--');
update tree_parent_child t set t.need_sum = (select nvl(sum(need_sum),0)+:new.need_sum-:old.need_sum from tree_parent_child where parentid= :new.parentid )
where id= :new.parentid;
commit;
end if;
end trgger_parent_child_sum;
after insert or update on tree_parent_child
for each row
declare
-- 自发事物
PRAGMA AUTONOMOUS_TRANSACTION;
begin
if inserting then
dbms_output.put_line('--inserting---');
update tree_parent_child t set t.need_sum = (select nvl(sum(need_sum),0)+:new.need_sum from tree_parent_child where parentid= :new.parentid )
where id= :new.parentid;
commit;
elsif updating then
dbms_output.put_line('----updating--');
update tree_parent_child t set t.need_sum = (select nvl(sum(need_sum),0)+:new.need_sum-:old.need_sum from tree_parent_child where parentid= :new.parentid )
where id= :new.parentid;
commit;
end if;
end trgger_parent_child_sum;