/*create table account(id serial primary key, balance double precision not null default(0));
create table ledger(id serial primary key, account int not null, amount double precision not null, constraint fk_ledger_account foreign key (account) references account(id));*/
/*
select * from account order by 1;
select * from ledger;

-- delete from ledger;
*/

create or replace function func_trg_ledger() returns trigger
 as $$
 declare    newAmount numeric(15,4);
        oldAmount numeric(15,4);
        accountid int;
 begin
   if TG_OP = 'DELETE' then
    newAmount :=0;
    oldAmount :=OLD.amount;
    accountid := OLD.account;
   elsif TG_OP = 'INSERT' then
    newAmount :=NEW.amount;
    oldAmount :=0;
    accountid := NEW.account;
   else
    newAmount :=NEW.amount;
    oldAmount :=OLD.amount;
    accountid := NEW.account;
   end if;

    update account set balance = balance + newAmount - oldAmount
    where id = accountid;

   return null;
 end;
$$ language plpgsql;

/*

--DROP TRIGGER trg_ledger ON ledger;

create trigger trg_ledger after insert or update or delete on ledger
 for each row execute procedure func_trg_ledger();
 */

 insert into ledger(account,amount) values(1,95);

 begin transaction;
 rollback transaction;
 commit transaction;

 update ledger set amount = amount - 1