 Raise Form_trigger_failure dosent Rollback all changes made [message #478535] |
Sun, 10 October 2010 09:21  |
karthikraj
Messages: 27
Registered: August 2005
|
Junior Member |
|
|
Let me explain the issue in detail...and thanks for the readers patience..
I have a database block in a form, where i choose a particular record and click on a button1 , which in turn opens a small canvas where i get few values from the user and click on a button2 in that small canvas itself.
The code inside this button2 is updating the table in the backend which is the source of the main database block. After this update, i immediately call a package, which creates a Tranasation (Adjustment txn) and during which the system encounters an error in this process and control goes to ON-ERROR trigger at form level, where RAISE FORM_TRIGGER_FAILURE is executed.
I have used the Commit at the end of the button2 trigger, expecting once the ADJ is created without any errors the commit has to happen and nowhere else the commit stmt exists for sure.
As per my assumption, this Raise form_trigger_failure should have rolled back the ADJ txn that was created as well the update that has happened in the main DB block. But surprisingly, i saw the ADJ txn rolledback wheareas the first update is not rolled back, since when i requeried the base DB block, i was able to see the updated values there.
Why this is happening?
I browsed the web and found that the Raise form_trigger_failure does not rollback data always. It behaves differently based on the place it has been called. Like, in a button trigger or in an Text item trigger.
Kindly help..
|
|
|
|
|
|
| Re: Raise Form_trigger_failure dosent Rollback all changes made [message #478576 is a reply to message #478541] |
Mon, 11 October 2010 00:21  |
karthikraj
Messages: 27
Registered: August 2005
|
Junior Member |
|
|
Hi,
Thanks for your reply..
When i requery the DB block i was able to see the changes with the new updated values, wheareas i was not able to see them at the backend, which means the update is still existing only for that session and the COMMIT has really not happened. Also if i exit the form and come back, the changes will not be there..
Thanks again..
|
|
|
|
| Re: Raise Form_trigger_failure dosent Rollback all changes made [message #478583 is a reply to message #478541] |
Mon, 11 October 2010 01:39 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
nikhilk411 wrote on Sun, 10 October 2010 18:18At the end of a transaction, and whenever you start a new transaction, oracle implicitly issues the commit command.
Does it?
Oracle Concepts book says:Quote:
A transaction is a logical unit of work that comprises one or more SQL statements run by a single user. According to the ANSI/ISO SQL standard, with which Oracle is compatible, a transaction begins with the user's first executable SQL statement. A transaction ends when it is explicitly committed or rolled back by that user.
(There's more to read in Transaction management section).
When does that "implicit commit" (you are talking about) happen?
FORM_TRIGGER_FAILURE exception stops the process from continuing; it doesn't rollback. You can still commit changes you made, if you wish.
In most cases, there's nothing to rollback because changes you do in a form are not posted to the database (until a COMMIT is issued) (or, possibly, if you explicitly use POST in a form).
What you might check / test is to use CLEAR_FORM (with no parameters, which is, in Forms, equivalent to ROLLBACK (see Forms Online Help System - search for "rollback")) or issue FORMS_DDL('ROLLBACK') and see how it behaves.
|
|
|
|
|
|
| Re: Raise Form_trigger_failure dosent Rollback all changes made [message #478590 is a reply to message #478587] |
Mon, 11 October 2010 03:05 |
karthikraj
Messages: 27
Registered: August 2005
|
Junior Member |
|
|
First i tried using Clear_block (also clear_block(no_validate) command, which cleared the block very well but did not rollback the update that was made...still i was able to requery and find the parent block with new updated values.
Then i tried using Forms_DDL(Rollback), using an exception block at the end of the same trigger, which rollbacks the entire data correctly as expected. But in this case the actual error message thrown from the back end package(eg. Account details not found so cannot post adjustment txn) is not shown in the screen. Display of the error message is taken care in the on-error trigger and since exception is handled at the trigger level control does not go to On-error trigger. I tried calling on_error procedure in the exception itself thinking it would display the actuall error message, but it shows me Invalid error 0000.
So both is not resolving the actuall issue..
(Now i get a basic doubt..
1. Clear_block will rollback the form level changes only or changes done at the backend table through procedures(in form) should also get rolled back
2. Control being in Form level, i call a package which does multiple insert and update and when an error encounterd we use RAISE_APPLICATION_ERROR. Does it take care of the rollback part, but untill which savepoint. I dont explicitly give any save point...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Raise Form_trigger_failure dosent Rollback all changes made [message #478621 is a reply to message #478607] |
Mon, 11 October 2010 05:51 |
karthikraj
Messages: 27
Registered: August 2005
|
Junior Member |
|
|
Actually, i want that update to be rolledback from that session because if the user press F10 by chance, the update will get committed (Without ADJ txn being created) which is actually wrong.
By main doubt is, while the ADJ txn that is created from backend pkg gets rolledback, why not the update getting rolledback.
|
|
|
|
| Re: Raise Form_trigger_failure dosent Rollback all changes made [message #479226 is a reply to message #478621] |
Fri, 15 October 2010 07:10 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
karthikraj wrote on Mon, 11 October 2010 11:51
By main doubt is, while the ADJ txn that is created from backend pkg gets rolledback, why not the update getting rolledback.
Probably because it's the error causing the rollback rather than a specific rollback command. Error rolls back to the start of the last db call (so call to the package in this case). It doesn't roll back the entire transaction.
Have you tried putting Forms_DDL(Rollback) in the on-error trigger.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
