Home » Developer & Programmer » Forms » Synchronising form values with database trigger-written values
| Synchronising form values with database trigger-written values [message #162771] |
Mon, 13 March 2006 10:26  |
mattpat
Messages: 5
Registered: March 2006
|
Junior Member |
|
|
I have hunted around Google and orafaq forum for an answer but I'm afraid I can't find anything matching my query. This has probably cropped up a few times before...
I have a database before-insert trigger on a table which sets the value of a field named contract_reference upon insertion. I have a typical and simple form based on the table where my block allows only inserts and not updates. Also, the contract_reference field is a non-enabled/non-insertable/non-updateable database item, for display purposes only. As expected, when I insert a record on the form and commit, the record is saved but the value of the contract_reference field in the form is not synchronised with the value written to the database, because the database trigger changed it. What's the best way of 'synchronising' the value of this field with the value in the database??
I have tried various ways using triggers like post-commit to update the item with the value just written to the table by using rowid etc. but none of them are problem-free!
The least problematic way I have found is simply to write a key-commit trigger that commits the form, checks for there being form_success, and re-executes the query if so, but then it jumps to the first record in the block. Grrr.
I need it to work just as if it were the form writing the value rather than the database trigger. So, for example, if I insert several records into the form, and there is an exception (eg. index violation) in writing *one* of the records, then they all fail to insert, and each instance of the contract_reference fields remain as null.
Any ideas?? Thanks in advance to anyone who can help...
|
|
|
|
|
|
| Re: Synchronising form values with database trigger-written values [message #163000 is a reply to message #162771] |
Tue, 14 March 2006 09:04  |
mattpat
Messages: 5
Registered: March 2006
|
Junior Member |
|
|
Firstly, it's not a key field for that table. The key field is another field which is (as per usual) displayed and generated on the form. The table trigger basically generates a record in *another* table, who's key is 'contract_reference' which is thrown back to my trigger and stored in the table. It is merely there so that the user can see what 'contract' was generated.
When I say non-insertable/non-updateable field, I mean having the insert/update allowed property set on the field - this does not prevent form triggers from writing data to the item / posting to the database etc., just prevents the user from editing it.
Creating a non-database field will not do a lot for me, as I still have the problem of the trigger creating a value which the form needs to synchronise with. Whether that be a another field not displayed which a non-database field gets its value from, or just having the database field displayed itself, doesn't make a lot of difference here. I just need my form to keep consistent with the value being written in the trigger.
|
|
|
|
|
|
|
|
| Re: Synchronising form values with database trigger-written values [message #163287 is a reply to message #163163] |
Wed, 15 March 2006 23:41 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Okay ... my suggestion was based on MOVING the logic from the trigger to the When-Validate-Item trigger.
IF you are NOT permitted to move the trigger code, then lets try an enhancement to a previous approach you had taken.
First, force a COMMIT on EACH row, then you don't have to worry about having five records entered and the third one causing the other four to fail. You know how to do that.
You said previously:| Quote: |
The least problematic way I have found is simply to write a key-commit trigger that commits the form, checks for there being form_success, and re-executes the query if so, but then it jumps to the first record in the block. Grrr.
|
Try this. Use get_block_property CURRENT_RECORD (v_curr_rec) to get the current line number, then the get_block_property TOP_RECORD (v_top_rec) to know which row is at the top of the screen. Now, do the form_commit, do the execute_query, do a "go_record (v_top_rec)", then "go_record (v_curr_rec)" and voila, you are right back where you started!
There is one wrinkle to this solution. Let's say there are 4 records displayed on a screen, for some reason record 2 is the top line and you have changed record 4 (on line 3). When you do the execute_query, you start on line 1, go to record 2 on line 2, and then go to record 4 on the bottom line. This is not the same as the screen from which you started. What you need to do is determine how many lines there are on the screen (get_block_property RECORDS_DISPLAYED (v_recs_disp)), if the destination row is less than or equal to the number of rows on a screen then you do a go_record (v_curr_rec+v_recs_disp) then go_record (v_top_rec) then go_record (v_curr_rec).
Your call - move the code or make the screen work for you. As long as you don't have any 'synchronize' commands in the logic flow there are NO EXTRA screen transmissions. Oracle Forms sends the LAST screen ONLY.
David
[Updated on: Wed, 15 March 2006 23:44] Report message to a moderator |
|
|
|
| Re: Synchronising form values with database trigger-written values [message #163331 is a reply to message #162771] |
Thu, 16 March 2006 04:44 |
mattpat
Messages: 5
Registered: March 2006
|
Junior Member |
|
|
Ok cool. Given that I do not want any records to be written to the database if one fails etc., I will take the second approach. I see why you suggested going to 'top record' first then to the current record - I do indeed have several records in the block displayed so to ensure the screen 'looks' the same I will follow your suggestion. The only other wrinkle I can think of with this way of doing it is that if the user *clears* records here and there previously saved to the database then requerying will bring them all up again, but i'll just disable that function for query/changed records 
To solve the wrinkle, upon successful commit I could navigate through all the records in the block, manually setting the field as required and forcing the record's state to query etc, then do your suggested go_record blabla to get back to where I was... then I think it will work exactly as if it were the form populating it. I'll save that for a rainy day when there's some more time!
Thanks for your input, I appreciate it 
[Updated on: Thu, 16 March 2006 04:47] Report message to a moderator |
|
|
|
|
|
| Re: Synchronising form values with database trigger-written values [message #163568 is a reply to message #163481] |
Fri, 17 March 2006 05:01 |
mattpat
Messages: 5
Registered: March 2006
|
Junior Member |
|
|
There are lots of other non-form processes that are inserting records into the same table. Records are written to it from database procedures ultimately controlled by another system, so there are several reasons why the trigger needs to be the process that inserts a record into another table, rather than the form. As it is the trigger inserting the record, it has been written such that the trigger is the process that picks off a new sequence number for the key for this other table. There is a lot of validation in the form, and anything that can be done easily to validate it (including primary key enforcement, data checks with othe tables etc) has been done in the form, so it is very unlikely that it could possibly fail to write the record to the database. On top of this, there are other processes that use the same table in a similar way (requiring the trigger on the table to work as such), so I can't easily change things, and in a way I like the fact that it is the trigger producing the sequence number for the record in the other table as it is the trigger that's inserting the other record. It's not normal for the form to know anything about the other system (like displaying this other reference etc), but it's handy for the user, and the user can 'drill down' straight to the relevant contract that was generated from the writing of this record etc.
There are good reasons for it working in such a way. It's not usual for one table trigger to insert a record into another table, but it is required in such circumstances. In short, there are several small 'systems' which work in their own way (a web interface for online clients, an ETC interface, a mid-office interface for collecting trades from agents within the firm, etc etc), and all of them ultimately get 'fed' into the one big system which manages all of the trades and does lots of things with them like sending them off to the CREST gateway.
PS. I don't think it is the case that *all* the records are being posted before finding out that one of them is wrong. It posts the records to the database one by one without a commit and if the database finds there is a problem with one of them it fails immediately before checking the rest and performs a rollback. So it's not as if there is much more work being done than otherwise can be. On top of that, almost all of the validation is done within the form.
[Updated on: Fri, 17 March 2006 05:15] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Sep 20 07:33:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
