When a user tries to delete a code/validation record, I would like to check to see if the code in that record has been used prior to the deletion attempt so I can display a message that is more meaningful than an Oracle constraint error.
For example: "This code cannot be deleted because it has been referenced in a course record. Please inactivate it instead."
Can anyone give me general advice on the approach to take?
My first thought was to create a dynamic action associated with the Delete button. I think there is probably a better way, however.
I prefer the notion of catching errors if they happen using the application error handler https://docs.oracle.com/en/database/oracle/application-express/20.2/htmdb/editing-application-attributes.html#GUID-B744BE26-69B6-4084-A217-114CF05A5A4B
The sample provides detail on how to translate any known constraints to a more user-friendly message.
The danger with creating a DA along the lines of your suggestion is there may be a time difference, however small, between the check & display, and the actual delete attempt.