Trigger a process (SQL update) when leaving an APEX page

Question

This is a very odd question but i wondered whether it was possible to trigger a process that runs an SQL UPDATE when the user leaves this specific page, either by clicking on the breadcrumb, tabs or back button?

I did setup the process to run on the 'Back' button; however i ran into the problem of: What if s/he clicks on the breadcrumb or a tab instead.

I have searched the net and posting this question was a last resort. I wondered if anyone can point me in the correct direction?

UPDATED POST

Page 1:
Includes a text field and a button. The user enters the category into the text field and clicks on the button to proceed to the next page. The button passes the value in the text field to a text field on Page 2.

Page 2:
Two regions on the page. One region holds information about the category - Category name from the previous page, category description, and the category revision number (which is what i'm trying to get working). The second region is a report that pulls the item name and number that are listed under the category in the category text field. A 'View' link is used on the report to load the edit page for the specific item selected. The 'View' link passes the 'category name' (from the category text field) and the 'item number' from the report to page 3.

Page 3
Two regions are used, first region: Lists the Item number which came from page 2 and name of the item (which i use a simple query to retrieve). In the second region: A report is used with two columns: a list of item properties in column 1, and a text field next to each item property in column 2 to hold the value that can be updated. The 'Apply Changes' button has some PlSql behind which looks for changes and updates where required. It updates the relevant fields that have changed, and it then takes the user back to Page 2 (Page showing user the items listed for the category intitally entered).

I cant increase the category revision on the 'apply changes' button on page 3 because the user may complete edits for items under the same category. So i dont know where i can increase the category revision by 1 per vist to a category. P.s I already increment the revision number for each item by 1 if the info has been changed using the 'apply changes' button on page 3.

If this is a data entry form, wouldn't it be better if there was a check to see if changes were made and prompt the user with that instead of firing an update? — Tom
@Tom I did find a blog post about checking for changes using Javascript; however the page is only showing information, no editing is completed on the page. Actually i think it might have been your blog. — Blob
Why would you actually want to fire an update when the page (in any way) is left by the user? Does visiting the page have to trigger something special? Do they need to interact in a way? Why, if there is no entry, is it required to run some process? — Tom
@Tom Sorry for the delay in reply. Its because products are catorgised into groups. User searches for a category (page 1) which returns a list of products in the group (page 2). I want to know how often a category is changed. A report is used to list the products which links to an edit page (page 3). Many products in a category could be edited in one sitting, meaning the category revision should only increase by 1, and not 3 (if 3 products were edited). Hopefully that makes sense. — Blob
That changes a lot about how to interpret the question: i thought that you'd want to fire an update process on an edit page even though the user would not have pressed an APPLY_CHANGES button (or the likes); which is something that @Matthew Moisen was aiming at. Your comment seems to indicate that you want to keep track of something instead: category changes. That still isn't easy to answer to though. Could you provide some more information about how exactly page 2 and 3 are set up, preferably with the used tables/data structure? — Tom

Matthew Moisen Matthew Moisen · Accepted Answer · 2013-01-17T22:04:11

One way of doing this is by updating the session state of the page items (without submitting the page as normal) via ajax and by calling an Application Process which updates the table, when called by a javascript onbeforeunload.

Create an Application Process to fire On Demand, named UpdatePageXOnExit or something. In the PL/SQL block of that Application Process, use SQL update commands with the item state of each item on the page that you wish to update; something like the following:

BEGIN
    UPDATE table_name SET col1 = :PX_ITEM_1, col2 = :PX_ITEM_2, col3 = :PX_ITEM3, col4 = :PX_ITEM_4, col5 = PX_ITEM_5 
WHERE (condition... I would suggest ROWIDs to simplify changes to the 
       fields of the PK; otherwise you will have to create additional
       page items for each field of the PK, then set those items to
       the values of the PK when the page loads using a before
       header page process, and then use those values in this where
       clause like this: where col1 = :PX_ORIGINAL_PK_FIELD_1 AND col2 =
       :PX_ORIGINAL_PK_FIELD_2 );
END ;

Go to the page settings for that particular page, and in the JavaScript: Execute On Page Load section, write something like the following:

window.onbeforeunload = updateOnExit;

And in the JavaScript Function and Global Variables Declaration, write something like the following:

function updateOnExit() {
    var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=UpdatePageXOnExit',&APP_PAGE_ID.);
    get.add('PX_ITEM_1',$x('PX_ITEM_1').value)
    get.add('PX_ITEM_2',$x('PX_ITEM_2').value)
    get.add('PX_ITEM_3',$x('PX_ITEM_3').value)
    get.add('PX_ITEM_4',$x('PX_ITEM_4').value)
    get.add('PX_ITEM_5',$x('PX_ITEM_5').value)
    gReturn = get.get();
    get = null;
    // Comment out the following line or not, as per the note in the following paragraph
    return 'Are you sure you would like to leave the page?';
}

This last piece is the AJAX that updates the session states of the page items on the page that have been changed on the user's end but that haven't been uploaded to the servers end which normally occurs during page submission. Note: If you comment out the return statement, than there will be no warning box that will allow the user to stay on the page. If that is what you prefer, go for it. If you chose to use the warning box, the ajax will execute the page process even if the user chooses to stay on the page.

Trigger a process (SQL update) when leaving an APEX page

1 Answers