From: "betty" <liongliong@telkom.net>
Subject: Re: trigger after with cursor
Date: Tue, 18 Feb 2003 18:37:04 +0700
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 82
Message-ID: <b2t5ab$12q7$1@news.hub.org>
References: <3E51F36A.75F42D3@rodos.fzk.de>
To: pgsql-sql@postgresql.org

I mean are there any solution for Using  "Update" statement
inside 'trigger after update'..??? for update it's table self.
rather than using "CURSOR"

thank's a lot
betty

"Christoph Haller" <ch@rodos.fzk.de> wrote in message
news:3E51F36A.75F42D3@rodos.fzk.de...
> >
> > 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html