>
> This problem can be handle using with rowtype, etc:
> create or replace function fn_tr_ai_pdcblc() returns trigger as '
> declare
> c_pdcblc cursor for select * from t_pdcblc where
fc_branch=new.fc_branch
> and fc_bankacct=new.fc_bankacct and fd_trxdate>=new.fd_trxdate;
> row_pdcblc t_pdcblc%rowtype;
> balance decimal(30,5);
> ctr int;
> begin
> balance := 0;
> ctr := 0;
> open c_pdcblc;
> for c_pdc in 1..(select count(*) from t_pdcblc where
> fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct and
> fd_trxdate>=new.fd_trxdate) loop
> fetch c_pdcblc into row_pdcblc;
> if (select count(*) from t_pdcblc where
fc_branch=new.fc_branch and
> fc_bankacct=new.fc_bankacct and fd_trxdate<new.fd_trxdate)>0 and ctr=0
then
> ctr := ctr + 1;
> select fm_balance into balance from t_pdcblc where
> fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct
> and
> fd_trxdate=(select max(fd_trxdate) from t_pdcblc where
> fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct and
> fd_trxdate<new.fd_trxdate);
> end if;
> balance := balance+row_pdcblc.fm_masuk-row_pdcblc.fm_keluar;
> row_pdcblc.fm_balance := balance;
> update t_pdcblc set fm_balance=row_pdcblc.fm_balance where
> fc_branch=row_pdcblc.fc_Branch and fc_bankacct=row_pdcblc.fc_bankacct
and
> fc_trxno=row_pdcblc.fc_trxno and fn_nomor=row_pdcblc.fn_nomor;
> end loop;
> close c_pdcblc;
> return new;
> end;' language 'plpgsql';
>
> create trigger tr_ai_pdcblc after insert on d_transaksi.t_pdcblc for
each
> row execute procedure fn_tr_ai_pdcblc();
>
Betty,
It's really hard to read your codings because all is lowercase.
> Now this problem trigger update for updated field in this table.
And I do not understand what's the problem you are referring to in your
2nd mail.
But, what came to my mind at once is:
Why do you UPDATE explicitly?
The documentation says:
If a non-NULL value is returned then the
operation proceeds with that row value. Note that returning a row value
different from the original value of NEW
alters the row that will be inserted or updated.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
It is possible to replace single values directly in NEW and return that,
or to build a complete new
record/row to return.
Regards, Christoph
