Thread: trigger after with cursor
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_branchand 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
> > 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
Hi List, I am using sequence to generate to a list of unique key id that will be used as the primary key for some tables. My question is when the sequence wraps over to the minimum value(1), how can I ensure that the next id is not in the tables? Regards, Raymond Pau __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com
On Tue, Feb 18, 2003 at 02:01:34 -0800, Raymond Pau <rpau@yahoo.com> wrote: > Hi List, Please don't start new threads by replying to other messages on the list. This screws up threading of messages, which is useful for following specific discussions. > I am using sequence to generate to a list of unique > key id that will be used as the primary key for some > tables. > > My question is when the sequence wraps over to the > minimum value(1), how can I ensure that the next id is > not in the tables? My suggestion is to use the bigserial type. This uses bigint as the underlying type and you are not likely to roll over a 64 bit integer unless you are wasting a lot of serial values for each one you actually use.
Raymond, > I am using sequence to generate to a list of unique > key id that will be used as the primary key for some > tables. > > My question is when the sequence wraps over to the > minimum value(1), how can I ensure that the next id is > not in the tables? Do you really expect more than 2.4 billion rows? -- Josh Berkus Aglio Database Solutions San Francisco
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
--- 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
--- Josh Berkus <josh@agliodbs.com> wrote: > Raymond, > > > I am using sequence to generate to a list of > unique > > key id that will be used as the primary key for > some > > tables. > > > > My question is when the sequence wraps over to the > > minimum value(1), how can I ensure that the next > id is > > not in the tables? > > Do you really expect more than 2.4 billion rows? No, not really. I guess my worries are groundless after all. Thanks. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/