OraFAQ Forum: Forms » getting items value after commit

Home » Developer & Programmer » Forms » getting items value after commit (forms builder 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

getting items value after commit [message #415135]

Sat, 25 July 2009 03:48

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

hi, i have a form where i have to do a commit, in which some of the values are controlled by a database trigger (status and date), and when i do the commit (commit_form;), it saves ok, but is there any way that i can automaticly display the values that the trigger controls? (kinda like a refresh of the same record after the commit, sorry for the lousy explanation my english isn't perfect xD) thanks!

Report message to a moderator

Re: getting items value after commit [message #415149 is a reply to message #415135]

Sat, 25 July 2009 06:34

ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai

Junior Member

Could you tell me the code of your trigger. And if you could also confirm whether that block is a database block or not.

Ali

Report message to a moderator

Re: getting items value after commit [message #415172 is a reply to message #415135]

Sat, 25 July 2009 14:49

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

hi, thanks for the reply, yes, it is a database block, the trigger is not on the form tho, it's on the database (inserted through the oracle desing editor, on a pl/sql insertion trigger), and the block id is controlled by a sequence, so when i commit it does those things internally, and the form does not reflect the changes inmediatly, so my guess is i have to write a post-insert trigger on forms or something like that? i've tried that but no luck (and i have no trigger for refreshing the info on screen because i don't know which command i should use >_<)

Report message to a moderator

Re: getting items value after commit [message #415173 is a reply to message #415172]

Sat, 25 July 2009 15:37

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

i have a form where i have to do a commit

OK then, this is handled by KEY-COMMIT trigger. Therefore, simply execute a query after committing. Something like this:

-- KEY-COMMIT trigger:

commit;
execute_query;

Eventually, you might need to set querying criteria. If so, check how to set it by using SET_BLOCK_PROPERTY built-in, its DEFAULT_WHERE (or ONETIME_WHERE) clause.

Report message to a moderator

Re: getting items value after commit [message #415179 is a reply to message #415173]

Sat, 25 July 2009 16:11

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

yeah, i already did the commit and the execute query (they are on the "Guardar" button trigger), the problem is that i have to show the same block i just saved (and if i do the execute query it just shows me everything, and since i have just 1 field to show.. well, you get the idea), with the records that got updated on the database, in the screenshot you can see what i mean, because the "estado" record poplist is in blank (but when you save, the database trigger sets it as "Iniciado", that's why i need to refresh, to reflect the changes made by the database trigger)

that is actually what i need, something like doing a refresh after commit, showing the exact same block i just saved

Attachment: screen.JPG
(Size: 59.65KB, Downloaded 655 times)

[Updated on: Sat, 25 July 2009 16:12]

Report message to a moderator

Re: getting items value after commit [message #415180 is a reply to message #415179]

Sat, 25 July 2009 16:30

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is why I have said that you might need to set DEFAULT_WHERE (or ONETIME_WHERE) block property in order to fetch exactly the same data as you had on the screen during COMMIT.

Report message to a moderator

Re: getting items value after commit [message #415181 is a reply to message #415180]

Sat, 25 July 2009 17:07

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

could you provide me with an example of that? never used that function, sorry for all the trouble, thanks

Report message to a moderator

Re: getting items value after commit [message #415199 is a reply to message #415181]

Sun, 26 July 2009 04:41

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Open Forms Help and search for SET_BLOCK_PROPERTY. It is all in there. Also, search this forum for the same built-in; there've been examples here as well.

Report message to a moderator

Re: getting items value after commit [message #415246 is a reply to message #415135]

Sun, 26 July 2009 14:40

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

k, thanks a lot for your help!, you saved me big time

Report message to a moderator

Re: getting items value after commit [message #415264 is a reply to message #415246]

Sun, 26 July 2009 23:47

ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai

Junior Member

I got that you were talking about Database Trigger. I wanted the trigger code coz when I tried the same here I got the tables mutating error. Thats the reason I wanted to see the code. I created a form based on table TEMP and created an After Insert Trigger. I wrote an update on TEMP table in the trigger. But it did not work and gave me the error. So could you just tell me how you achieved it?

Ali.

Report message to a moderator

Re: getting items value after commit [message #415323 is a reply to message #415135]

Mon, 27 July 2009 04:48

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

If a database trigger is modifying values set in your datablock then what you need to do is set the block property DML Returning Value to Yes.
No requerying should be necessary.

Report message to a moderator

Re: getting items value after commit [message #415354 is a reply to message #415264]

Mon, 27 July 2009 06:22

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

hi, actually i just used the command that Littlefoot suggested, it would be something like

commit_form;
SET_BLOCK_PROPERTY('PROYECTOS', DEFAULT_WHERE, ' WHERE nombre = ' || '''' || :proyectos.nombre || '''' );
go_block('proyectos');
execute_query(no_validate);
SET_BLOCK_PROPERTY('PROYECTOS', DEFAULT_WHERE, '' );

(i'm doing the commit with a button, so i just have it set on a when-button-pressed trigger), also that's asumming you have only one entry using the same name, else it may not return the one you want, you can do several on the same set_block_property tho, hope that helped

Report message to a moderator

Re: getting items value after commit [message #415356 is a reply to message #415323]

Mon, 27 July 2009 06:29

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

cookiemonster wrote on Mon, 27 July 2009 04:48

If a database trigger is modifying values set in your datablock then what you need to do is set the block property DML Returning Value to Yes.
No requerying should be necessary.

actually i asummed there should be a way to make forms update the values automaticly, even tho in this case it is inserting values, not just modifying (don't know if that makes the difference), just tried it and it didn't work (must have done something wrong, it's 8 am here and i haven't slept in like ... i forgot how much lol), i'll try it again after i get some sleep, the solution Littlefoot suggested works as well, but it's good to know several ways of doing things just in case, thanks for the help!

Report message to a moderator

Re: getting items value after commit [message #415362 is a reply to message #415135]

Mon, 27 July 2009 06:50

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

DML Return should work for this. It behaves the same for inserts and updates. It basically appends a RETURNING clause to the insert/update statement that the block auto-generates. Look up the syntax if you're not sure what it does.

Report message to a moderator

Re: getting items value after commit [message #415437 is a reply to message #415362]

Mon, 27 July 2009 19:00

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have you considered having that 'default_where' as the standard 'where' clause for your block?

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values. You COULD do an execute-query but repopulating the complete block is IMHO "over-kill".

David

Report message to a moderator

Re: getting items value after commit [message #415533 is a reply to message #415362]

Tue, 28 July 2009 03:30

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

cookiemonster wrote on Mon, 27 July 2009 06:50

DML Return should work for this. It behaves the same for inserts and updates. It basically appends a RETURNING clause to the insert/update statement that the block auto-generates. Look up the syntax if you're not sure what it does.

yeah, actually it works, i tried it earlier and it worked alright, must have been my loss of sleep messing with my head lol, thanks

Report message to a moderator

Re: getting items value after commit [message #415539 is a reply to message #415437]

Tue, 28 July 2009 03:37

keiiferrer
Messages: 9
Registered: July 2009

Junior Member

djmartin wrote on Mon, 27 July 2009 19:00

Have you considered having that 'default_where' as the standard 'where' clause for your block?

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values. You COULD do an execute-query but repopulating the complete block is IMHO "over-kill".

David

yup, i thought that as well, the good thing is that it only does it when it is needed (just on a new record insert, i have a condition checking that after commit), i already handed over the working project (and my teacher seemed to be ok with it, i had little time to correct that and some other errors on the project), but i'll keep it in mind when i have to do something similar again, thanks again for all the help! ^^

Report message to a moderator

Re: getting items value after commit [message #415547 is a reply to message #415437]

Tue, 28 July 2009 03:58

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

djmartin wrote on Tue, 28 July 2009 01:00

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values.

Isn't that likely to lead to "record has been updated by another user" errors?

Report message to a moderator

Re: getting items value after commit [message #415704 is a reply to message #415547]

Tue, 28 July 2009 20:02

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Make the display items non-database. Also, populate them in the Post-Query.

David

Report message to a moderator

Previous Topic:	FRM-40514: Operation requires a database connection.
Next Topic:	Insert data in one transaction.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 09:38:21 CDT 2024