Вот пример, только он длинный немного, но вот:
create table master (
master_id number(9)
, amount number(15, 4)
, constraint pk_master primary key (master_id)
)
/
create table detail (
detail_id number(9)
, master_id number(9) not null
, amount number(15, 4) not null
, constraint pk_detail primary key (detail_id)
, constraint fk_detail_master foreign key (master_id)
references master(master_id) on delete cascade
)
/
create or replace package pkg_master as
type tbl_master is table of master%rowtype index by binary_integer;
v_master tbl_master;
procedure update_amount;
end;
/
create or replace package body pkg_master as
procedure update_amount as
begin
if v_master.count > 0 then
for i in v_master.first..v_master.last loop
if v_master.exists(i) then
update master set
amount = nvl(amount, 0) + v_master(i).amount
where 1=1
and master_id = v_master(i).master_id;
end if;
end loop;
v_master.delete;
end if;
end;
end;
/
create or replace trigger taiudr_detail
after insert or update or delete on detail
for each row
begin
if inserting or updating then
pkg_master.v_master(:new.master_id).master_id := :new.master_id;
pkg_master.v_master(:new.master_id).amount :=
nvl(pkg_master.v_master(:new.master_id).amount, 0) + :new.amount;
end if;
if updating or deleting then
pkg_master.v_master(:old.master_id).master_id := :old.master_id;
pkg_master.v_master(:old.master_id).amount :=
nvl(pkg_master.v_master(:old.master_id).amount, 0) - :old.amount;
end if;
end;
/
create or replace trigger taiud_detail
after insert or update or delete on detail
begin
pkg_master.update_amount;
end;
/SQL> insert into master(master_id) values (1);
1 row inserted
SQL> insert into detail(detail_id, master_id, amount) values (1, 1, 10);
1 row inserted
SQL> select * from master;
MASTER_ID AMOUNT
---------- -----------------
1 10.0000
SQL> insert into detail(detail_id, master_id, amount) values (2, 1, 20);
1 row inserted
SQL> select * from master;
MASTER_ID AMOUNT
---------- -----------------
1 30.0000
SQL> update detail set amount = 40 where detail_id = 1;
1 row updated
SQL> select * from master;
MASTER_ID AMOUNT
---------- -----------------
1 60.0000
SQL> delete detail where detail_id = 2;
1 row deleted
SQL> select * from master;
MASTER_ID AMOUNT
---------- -----------------
1 40.0000
