1 / 20

Query Triggers

Query Triggers. Objectives. After completing this lesson, you should be able to do the following: Explain the processes involved in querying a data block Describe query triggers and their scope Write triggers to supplement query results and screen query conditions

whitney
Download Presentation

Query Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Triggers

  2. Objectives After completing this lesson, you should be able to do the following: • Explain the processes involved in querying a data block • Describe query triggers and their scope • Write triggers to supplement query results and screen query conditions • Control trigger action based on the form query status

  3. Query Processing Fire Pre-Query trigger Abort query on failure Construct SELECT... Perform query Fetch a row into a new record Flush record on failure Mark record as valid Fire Post-Query trigger Validate any record changes

  4. SELECT Statements Issued SELECT base_column, ..., ROWID INTO :base_item, ..., :ROWID FROM base_table WHERE default_where_clause AND (example_record_conditions) AND (query_where_conditions) ORDER BY default_order_by_clause |query_where_order_by Slightly different for COUNT

  5. WHERE Clause • Three sources for the WHERE clause: • WHERE clause block property • Example Record • Query/Where dialog box • WHERE clauses are combined by the AND operator

  6. ORDER BY Clause • Two sources for the ORDER BY clause: • ORDER BY clause block property • Query/Where dialog box • Second source for ORDER BY clause overrides the first one

  7. Pre-Query Trigger • Defined at block level • Fires once, before query is performed IF TO_CHAR(:S_ORD.ID)|| TO_CHAR(:S_ORD.DATE_ORDERED)|| TO_CHAR(:S_ORD.DATE_SHIPPED) IS NULL THEN MESSAGE(’You must query by Order ID or Date’); RAISE form_trigger_failure; END IF;

  8. Post-Query Trigger • Fires for each fetched record (except during array processing) • Use to populate nondatabase items and calculate statistics SELECT COUNT(ord_id) INTO :S_ORD.lineitem_count FROM S_ITEM WHERE ord_id = :S_ORD.id;

  9. Using SELECT Statements in Triggers • Form Builder variables are preceded by a colon. • The query must return one row for success. • Code exception handlers. • The INTO clause is mandatory, with a variable for each selected column or expression. • ORDER BY is not relevant.

  10. Query Array Processing • Reduces network traffic • Enables Query Array processing: • Enable Array Processing option • Set Query Array Size property • Query Array Size property • Query All Records property

  11. Coding for ENTER-QUERY Mode • Some triggers may fire in Enter-Query mode. • Set to fire in Enter-Query Mode property. • Test mode during execution with :SYSTEM.MODE • NORMAL • ENTER-QUERY • QUERY

  12. Coding for ENTER-QUERY Mode • Example • Some built-ins are illegal. • Consult online Help. • You cannot navigate to another record in the current form. IF :SYSTEM.MODE = ’NORMAL’THEN ENTER_QUERY; ELSE EXECUTE_QUERY; END IF;

  13. Overriding Default Query Processing Trigger On-Close On-Count On-Fetch Pre-Select On-Select Post-Select Do-the-Right-Thing Built-in COUNT_QUERY FETCH_RECORDS SELECT_RECORDS

  14. Overriding Default Query Processing • On-Fetch continues to fire until: • It fires without executing CREATE_QUERIED_RECORD. • The query is closed by the user or by ABORT_QUERY. • It raises FORM_TRIGGER_FAILURE. • On-Select replaces open cursor, parse, and execute phases.

  15. Obtaining Query Information at Run Time • SYSTEM.MODE • SYSTEM.LAST_QUERY • Contains bind variables (ORD_ID = :1) before SELECT_RECORDS • Contains actual values (ORD_ID = 102) after SELECT_RECORDS

  16. Obtaining Query Information at Run Time • GET_BLOCK_PROPERTY SET_BLOCK_PROPERTY • Get and set: DEFAULT_WHERE ORDER_BY QUERY_ALLOWED QUERY_HITS • Get only: QUERY_OPTIONS RECORDS_TO_FETCH

  17. Obtaining Query Information at Run Time • GET_ITEM_PROPERTY SET_ITEM_PROPERTY • Get and set: CASE_INSENSITIVE_QUERYQUERYABLEQUERY_ONLY • Get only: QUERY_LENGTH

  18. Summary • A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions. • A Post-Query trigger fires as each record is fetched (except array processing). Use it to perform calculations and populate additional items.

  19. Summary • Some triggers can fire in both Normal and Enter Query modes: • Test the current mode with SYSTEM.MODE. • Some built-ins are illegal in Enter Query mode. • Obtain query information at run time: • SYSTEM.MODE • SYSTEM.LAST_QUERY

  20. Practice 17 Overview • This practice covers the following topics: • Populating customer names and sales representative names for each row of the S_ORD block • Populating descriptions for each row of the S_ITEM block • Disabling the effect of the Exit button in Enter Query mode • Adding two check boxes to enable case-sensitive and exact match query

More Related