From: betty <liongliong@telkom.net>
To: pgsql-sql@postgresql.org
Subject: Re: trigger after with cursor
Date: Mon, 17 Feb 2003 17:25:54 +0700
Message-ID: <b2qcp3$1hlf$1@news.hub.org> (raw)
References: <b2pphu$2n75$1@news.hub.org>
Hi...
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();
Now this problem trigger update for updated field in this table.
Please help me.
thank's
betty
"betty" <liongliong@telkom.net> wrote in message
news:b2pphu$2n75$1@news.hub.org...
> Hi..
>
> I try trigger after insert used cursor without rowtype or cursor 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;
> row_pdcblc t_pdcblc%rowtype;
> balance decimal(30,5);
> begin
> balance:=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 then
> balance:=fm_balance from t_pdcblc where fc_branch=new.fc_branch
> and fc_bankacct=new.fc_bankacct and fd_trxdate<new.fd_trxdate;
> end if;
> new.fm_balance :=
balance+row_pdcblc.fm_masuk-row_pdcblc.fm_keluar;
> (update field fm_balance at table t_pdcblc)
> balance := new.fm_balance;
> 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();
>
> result actually:
> table t_pdcblc for field fm_balance occupied value null, ex:
> fm_masuk fm_keluar fm_balance
> 1000 0
>
> this must:
> fm_masuk fm_keluar fm_balance
> 1000 0 1000
>
>
> Please help me...
>
> thanks
> betty
>
>
>
>
>
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: liongliong@telkom.net
Subject: Re: trigger after with cursor
In-Reply-To: <b2qcp3$1hlf$1@news.hub.org>
* 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