From: Christoph Haller <ch@rodos.fzk.de>
To: liongliong@telkom.net
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger after with cursor
Date: Tue, 18 Feb 2003 09:48:42 +0100
Message-ID: <3E51F36A.75F42D3@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, liongliong@telkom.net
Subject: Re: trigger after with cursor
In-Reply-To: <3E51F36A.75F42D3@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox