[web2py] Re: Web2py with postgresql and DB triggers
What does "#now do something with newreorderrow" do? On Thursday, 12 June 2014 14:12:45 UTC-5, Chris Farrar wrote: > > Hi, > > I'm struggling with a strange issue. > > I have a web2py application with a simple data model. I have a table > called "reorder". The user is able to interact with "reorder" using a > SQLFORM.grid. I have implemented the oncreate call back. My oncreate > callback gets the id of the newly inserted reorder record then does > something with it. > > > def oncreatereorderhandler(form): > myid = form.vars.id > newreorderrow = db.reorder(myid) > #now do something with newreorderrow > > > Generally this works well. form.vars.id correctly corresponds to the id > of the newly inserted row and life is happy. The problem occurs when I put > a database trigger on the reorder table that fires after the insert. > Whenever this trigger is on the database the value of form.vars.id is > nonsense. The actual id of the inserted row is fine it's just that the > value of form.vars.id in now way corresponds to it. For example, after > the trigger is added, the values of form.vars.id will be 4 for the very > next insert. For every insert after the value will increment by 3 (eg 4, > 7, 10, 13, etc). When I remove the trigger everything returns to normal. > > How is form.vars.id populated and how would a DB trigger interfere? > > Here's the trigger and associated procedure. It was generated by > symmetricds. > > CREATE TRIGGER sym_on_i_for_rrdr_trggr_crp > AFTER INSERT > ON reorder > FOR EACH ROW > EXECUTE PROCEDURE fsym_on_i_for_rrdr_trggr_crp(); > > > -- Function: fsym_on_i_for_rrdr_trggr_crp() > > -- DROP FUNCTION fsym_on_i_for_rrdr_trggr_crp(); > > CREATE OR REPLACE FUNCTION fsym_on_i_for_rrdr_trggr_crp() > RETURNS trigger AS > $BODY$ > > begin > >if 1=1 and > "public".sym_triggers_disabled() = 0 then > > insert into "public".sym_data > > (table_name, > event_type, trigger_hist_id, row_data, channel_id, transaction_id, > source_node_id, external_data, create_time) >values( > > > 'reorder', > > 'I', > > > 8, > > > case when new."uuid" is null then '' else '"' || > replace(replace(cast(new."uuid" as varchar),$$\$$,$$\\$$),'"',$$\"$$) || > '"' end||','|| > case when new."id" is null then '' else '"' || > cast(cast(new."id" as numeric) as varchar) || '"' end||','|| > case when new."modified_on" is null then '' else '"' || > to_char(new."modified_on", '-MM-DD HH24:MI:SS.US') || '"' end||','|| > case when new."productid" is null then '' else '"' || > replace(replace(cast(new."productid" as varchar),$$\$$,$$\\$$),'"',$$\"$$) > || '"' end||','|| > case when new."reorderquantity" is null then '' else '"' || > cast(cast(new."reorderquantity" as numeric) as varchar) || '"' end||','|| > case when new."receivedquantity" is null then '' else '"' || > cast(cast(new."receivedquantity" as numeric) as varchar) || '"' end||','|| > case when new."reorderdate" is null then '' else '"' || > to_char(new."reorderdate", '-MM-DD HH24:MI:SS.US') || '"' end||','|| > case when new."expectedreceivedate" is null then '' else '"' || > to_char(new."expectedreceivedate", '-MM-DD HH24:MI:SS.US') || '"' > end||','|| > case when new."vendorso" is null then '' else '"' || > replace(replace(cast(new."vendorso" as varchar),$$\$$,$$\\$$),'"',$$\"$$) > || '"' end||','|| > case when new."customerpo" is null then '' else '"' || > repla
[web2py] Re: Web2py with postgresql and DB triggers
Hi Massimo, I'm simply recording in another table that an insert has been made in the reorder table. Here's the full function: def oncreatereorderhandler(form): myid = form.vars.id newreorderrow = db.reorder(myid) #now do something with newreorderrow db.trans.insert(ProductID=newreorderrow.uuid, TransactionType='New Reorder', Quantity=form.vars.Quantity, Notes='Initial Stock', UserID=auth.user.uuid ,TransactionTime=datetime.now()) Thanks for your help! On Friday, June 13, 2014 12:53:09 PM UTC-4, Massimo Di Pierro wrote: > > What does "#now do something with newreorderrow" do? > > On Thursday, 12 June 2014 14:12:45 UTC-5, Chris Farrar wrote: >> >> Hi, >> >> I'm struggling with a strange issue. >> >> I have a web2py application with a simple data model. I have a table >> called "reorder". The user is able to interact with "reorder" using a >> SQLFORM.grid. I have implemented the oncreate call back. My oncreate >> callback gets the id of the newly inserted reorder record then does >> something with it. >> >> >> def oncreatereorderhandler(form): >> myid = form.vars.id >> newreorderrow = db.reorder(myid) >> #now do something with newreorderrow >> >> >> Generally this works well. form.vars.id correctly corresponds to the id >> of the newly inserted row and life is happy. The problem occurs when I put >> a database trigger on the reorder table that fires after the insert. >> Whenever this trigger is on the database the value of form.vars.id is >> nonsense. The actual id of the inserted row is fine it's just that the >> value of form.vars.id in now way corresponds to it. For example, after >> the trigger is added, the values of form.vars.id will be 4 for the very >> next insert. For every insert after the value will increment by 3 (eg 4, >> 7, 10, 13, etc). When I remove the trigger everything returns to normal. >> >> How is form.vars.id populated and how would a DB trigger interfere? >> >> Here's the trigger and associated procedure. It was generated by >> symmetricds. >> >> CREATE TRIGGER sym_on_i_for_rrdr_trggr_crp >> AFTER INSERT >> ON reorder >> FOR EACH ROW >> EXECUTE PROCEDURE fsym_on_i_for_rrdr_trggr_crp(); >> >> >> -- Function: fsym_on_i_for_rrdr_trggr_crp() >> >> -- DROP FUNCTION fsym_on_i_for_rrdr_trggr_crp(); >> >> CREATE OR REPLACE FUNCTION fsym_on_i_for_rrdr_trggr_crp() >> RETURNS trigger AS >> $BODY$ >> >> begin >> >>if 1=1 and >> "public".sym_triggers_disabled() = 0 then >> >> insert into "public".sym_data >> >> (table_name, >> event_type, trigger_hist_id, row_data, channel_id, transaction_id, >> source_node_id, external_data, create_time) >>values( >> >> >> 'reorder', >> >> 'I', >> >> >> 8, >> >> >> case when new."uuid" is null then '' else '"' || >> replace(replace(cast(new."uuid" as varchar),$$\$$,$$\\$$),'"',$$\"$$) || >> '"' end||','|| >> case when new."id" is null then '' else '"' || >> cast(cast(new."id" as numeric) as varchar) || '"' end||','|| >> case when new."modified_on" is null then '' else '"' || >> to_char(new."modified_on", '-MM-DD HH24:MI:SS.US') || '"' end||','|| >> case when new."productid" is null then '' else '"' || >> replace(replace(cast(new."productid" as varchar),$$\$$,$$\\$$),'"',$$\"$$) >> || '"' end||','|| >> case when new."reorderquantity" is null then '' else '"' || >> cast(cast(new."reorderquantity" as numeric) as varchar) || '"' end||','|| >> ca
