-- счета клиентов
create table acc(id integer not null primary key, client integer not null, account_type integer not null, current_rest number default 0 not null);

-- движение по счетам клиентов
create table acc_move(id integer not null primary key, acc integer not null references acc(id), dt date not null, value number not null);

-- тип для хранения одного движения
create type t_acc_move as object(acc integer, value number);
/

-- коллекция для накопления изменений
create type tt_acc_move as table of t_acc_move;
/

-- триггер для подсчета остатка
CREATE OR REPLACE TRIGGER trg$acc_move_rest
  for insert or update or delete on acc_move
  compound trigger

  l_moves tt_acc_move;

  before statement is
  begin
    l_moves := tt_acc_move();
  end before statement;

  before each row is
    l_idx    number := l_moves.count + 1;
    l_values t_acc_move := t_acc_move(0, 0);
  begin
    l_moves.extend(1);
    if inserting
    then
      l_values.acc := :new.acc;
      l_values.value := :new.value;
    elsif updating
    then
      if :new.acc = :old.acc
      then
        l_values.acc := :new.acc;
        l_values.value := :new.value - :old.value;
      else
        l_moves.extend(1);

        l_values.acc := :old.acc;
        l_values.value := - :old.value;
        l_moves(l_idx + 1) := l_values;

        l_values.acc := :new.acc;
        l_values.value := :new.value;
      end if;
    elsif deleting
    then
      l_values.acc  := :old.acc;
      l_values.value := - :old.value;
    end if;
    l_moves(l_idx) := l_values;
  end before each row;

  after statement is
  begin
    merge into acc a
    using (select acc,
                  sum(value) value
             from table(l_moves)
            group by acc) t
    on (t.acc = a.id)
    when matched then
      update
         set a.current_rest = a.current_rest + t.value;
  end after statement;

end;
/