X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DBB3E474E53
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 05:16:10 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 72A6A474E42
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 05:16:10 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 55AA843A996; Mon, 17 Feb 2003 05:16:10 -0500 (EST)
From: "betty" <liongliong@telkom.net>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: trigger after with cursor
Date: Mon, 17 Feb 2003 17:25:54 +0700
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 110
Message-ID: <b2qcp3$1hlf$1@news.hub.org>
References: <b2pphu$2n75$1@news.hub.org>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/378
X-Sequence-Number: 11776

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