Re: trigger after with cursor

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(at)rodos(dot)fzk(dot)de> wrote in message
news:3E51F36A(dot)75F42D3(at)rodos(dot)fzk(dot)de(dot)(dot)(dot)
> >
> > 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