pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger after with cursor
5+ messages / 3 participants
[nested] [flat]
* trigger after with cursor
@ 2003-02-17 04:57 betty <liongliong@telkom.net>
0 siblings, 1 reply; 5+ messages in thread
From: betty @ 2003-02-17 04:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: trigger after with cursor
@ 2003-02-17 10:25 betty <liongliong@telkom.net>
parent: betty <liongliong@telkom.net>
0 siblings, 0 replies; 5+ messages in thread
From: betty @ 2003-02-17 10:25 UTC (permalink / raw)
To: 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_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
>
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: trigger after with cursor
@ 2003-02-18 08:48 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Haller @ 2003-02-18 08:48 UTC (permalink / raw)
To: liongliong@telkom.net; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: trigger after with cursor
@ 2003-02-18 11:37 betty <liongliong@telkom.net>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 5+ messages in thread
From: betty @ 2003-02-18 11:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: trigger after with cursor
@ 2003-02-18 20:21 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: betty <liongliong@telkom.net>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Eckermann @ 2003-02-18 20:21 UTC (permalink / raw)
To: betty <liongliong@telkom.net>; pgsql-sql
--- betty <liongliong@telkom.net> wrote:
> I mean are there any solution for Using "Update"
> statement
> inside 'trigger after update'..??? for update it's
> table self.
> rather than using "CURSOR"
>
No. The only values you can change are those in the
NEW row, by assigning values to them in your
procedure.
I could not tell from your code just what you are
trying to do: if you could tell us more about that,
perhaps someone can advise you.
__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-02-18 20:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-17 04:57 trigger after with cursor betty <liongliong@telkom.net>
2003-02-17 10:25 ` betty <liongliong@telkom.net>
2003-02-18 08:48 Re: trigger after with cursor Christoph Haller <ch@rodos.fzk.de>
2003-02-18 11:37 ` betty <liongliong@telkom.net>
2003-02-18 20:21 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
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