Message-ID: <3E51F36A.75F42D3@rodos.fzk.de>
Date: Tue, 18 Feb 2003 09:48:42 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: liongliong@telkom.net
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger after with cursor
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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