From: betty <liongliong@telkom.net>
To: pgsql-sql@postgresql.org
Subject: Re: trigger after with cursor
Date: Tue, 18 Feb 2003 18:37:04 +0700
Message-ID: <b2t5ab$12q7$1@news.hub.org> (raw)
References: <3E51F36A.75F42D3@rodos.fzk.de>
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
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: <b2t5ab$12q7$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