From: | "betty" <liongliong(at)telkom(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | trigger after with cursor |
Date: | 2003-02-17 04:57:37 |
Message-ID: | b2pphu$2n75$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Loh | 2003-02-17 06:16:44 | Trigger that will execute external program |
Previous Message | Joe Conway | 2003-02-17 04:02:55 | Re: Table Pivot |