Re: trigger after with cursor
От | betty |
---|---|
Тема | Re: trigger after with cursor |
Дата | |
Msg-id | b2qcp3$1hlf$1@news.hub.org обсуждение исходный текст |
Список | pgsql-sql |
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_branchand fc_bankacct=new.fc_bankacct and fd_trxdate<new.fd_trxdate)>0 and ctr=0 then ctr := ctr + 1; select fm_balanceinto 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 > > > > >
В списке pgsql-sql по дате отправления: