Oracle Exam 1Z0-151 Topic 6 Question 4 Discussion

About controlling queries with Pre-Query and Post-Query triggers

The Pre-Query and Post-Query triggers allow control over query processing. They can be defined at the form or block level. Most often, attach them to specific blocks to control the query functionality of those blocks.

The Pre-Query trigger fires just before Form Builder issues the SELECT statement to the database, after the operator has defined the example record by entering query criteria in Enter Query mode.

Inside a Pre-Query trigger, the example record defined by the query criteria is the current record. This means that trigger code can read and set the values of items in the example record using standard :block_name.item_name syntax.

A Pre-Query trigger can be used to disallow query conditions that might be invalid. When a form is in Enter Query mode, normal validation is suspended and no validation triggers fire as they do in Normal mode. The Pre-Query trigger thus allows you to verify that any values entered by the operator are valid query conditions.

When invalid query conditions have been entered, you can abort the query by raising the FORM_TRIGGER_FAILURE built-in exception in the Pre-Query trigger.

You can also call SET_BLOCK_PROPERTY to modify the block's WHERE and ORDER BY clauses from within the Pre-Query trigger, to further restrict or order the records the query will retrieve.