Oracle 11Gex
Apex 5.0.2
ORDS 3/Glassfish 4.1
I have a page for management review of staff input. Staff create records and after management review, two processes are run: Auto MRU, and then a SendMail (for created and modified rows).
My issue is how to restrict duplicate records and duplicate emails, when the same record is being acted upon by two different 'management' users, at about the same time (within minutes of each other).
The 'review' form is based on a query that pulls records with a REVIEWED column that is null. The form includes this 'REVEIWED' field as a checkbox which once checked, sets its value in the database on submit of the page. So if multiple users are performing the 'review' process at the same time, they can both get the same list of records... And both can 'review' and attempt to update the same record.
I think I can prevent duplicate table rows by adding a 'Runtime Where Clause' (REVIEWED is null) to the MRU page - so that a check is made against the table at time of processing, to ensure the record hasn't been processed yet. This should catch situations where multiple users are acting on the same record... whichever gets to the DB first can record the record and the next user finds the record already addressed. My understanding is the 2nd user would receive an error indicating the record was already processed (based upon the error message I add). This would prompt them then to correct the affected row (uncheck the REVIEWED ckbox) BEFORE allowing the processing of any submitted rows.
My questions then are:
1) will adding the 'runtime where' clause act as I believe it will
2) will the 2nd user be prevented from sending an email on the affected record he is attempting to update when it errors?
Keep in mind the user will see 10 records and may be updating multiple records with each 'submit'; as well, there are 2 processes - the first is the MRU which would error out for the duplicate row(s) on submit... but will the 2nd process (SendMail) STILL be run for those errored rows? since they are 'modified' rows?
Thanks,
Rich