keep consistency among two tables

[Tony Winslow <to...@gmail.com>]

Sorry for bad title since I can't thing up a better one!

I doing a stock project which includes functions of portfolio and I have
the following two tables(other tables are omitted):

create table stock_holdings(
    holding_id int not null generated always as identity
    constraint holding_pk primary key,
    port_id int not null
    constraint holding_fk1 references port_records,
    symbol varchar(50) not null
    constraint holding_fk2 references stocks,
    shares int,
    total_cost double
);

create table port_trans(
    port_trans_id int not null generated always as identity
    constraint port_trans_pk primary key,
    port_id int not null
    constraint port_trans_port_fk references port_records,
    symbol varchar(50),
    tran_type int constraint tran_type_ck check (tran_type in (-1, 1)),
    tran_date date,
    shares int constraint port_trans_shares_ck check (shares >= 0),
    price double constraint price_ck check (price > 0),
    commission double constraint commission_ck check (commission >= 0),
    notes varchar(100)
);

shares and total_cost in stock_holdings are calculated according to
the change of port_trans.
For example:
 if record saying "port_id=1 sells 1000 shares of 'IBM' at price $1", then
the record in stock_holdings
 should set shares 1000 less and total_cost $1000 less respectively where
 symbol='IBM' and port_id=1. things are going like that and I think further
explanation maybe
 redundant and unnecessary.

My problem now is how can I utilize triggers and stored procedures to make
modifications to
stock_holdings when port_trans change. I think leaving that work to
programmers rather than
database designers is not a good idea.

Thank you all in advance!