POST-QUERY Trigger in Oracle Forms

When working with Oracle Forms, managing how data is handled during query execution is essential for building efficient and user-friendly applications. One important trigger that plays a big role in this process is the POST-QUERY Trigger. This trigger allows developers to take actions on each record after it has been fetched from the database. In this article, you will learn what the POST-QUERY Trigger is, why it is useful, how it works, examples of its implementation, and best practices to follow.

What is the POST-QUERY Trigger?

The POST-QUERY Trigger is a block-level trigger in Oracle Forms that fires once for every record retrieved from the database during a query.

Unlike the PRE-QUERY Trigger, which modifies conditions before the query runs, POST-QUERY executes after the database has returned a record but before it is displayed in the form. This allows developers to enrich or manipulate the data for display purposes.

Key points about the POST-QUERY Trigger:

• It is defined at the block level.
• Fires once for each record retrieved by a query.
• Used for post-processing data, such as formatting, fetching related details, or calculating additional values.
• Cannot modify the query itself (that is done in PRE-QUERY).

Purpose of the POST-QUERY Trigger

The primary purpose of the POST-QUERY Trigger is to process or enhance the data after it is retrieved but before it is shown to the user.

Some common use cases include:

• Displaying additional information that is not stored in the base table.
• Formatting data for better readability (e.g., showing currency symbols, derived labels).
• Fetching related data from other tables to display in non-database items.
• Applying security rules to hide or mask certain details based on user privileges.
• Calculating derived fields that depend on retrieved values.

When Does the POST-QUERY Trigger Fire?

The POST-QUERY Trigger executes in the following sequence:

1. The query is executed on the block.
2. The database returns the first record.
3. POST-QUERY fires for that record.
4. The record is displayed in the block.
5. The process repeats for each additional record retrieved.

This means if 100 rows are retrieved, the POST-QUERY Trigger fires 100 times, once per record.

Difference Between PRE-QUERY and POST-QUERY

It is common to confuse these two triggers since they are often used together. Here is the difference:

• PRE-QUERY Trigger: Fires before the query runs. It is used to set conditions or filters.
• POST-QUERY Trigger: Fires after each record is retrieved. It is used for additional processing or display formatting.

In simple terms:

• PRE-QUERY = Prepare query conditions
• POST-QUERY = Process retrieved data

Example of POST-QUERY Trigger

Here’s a practical example that shows how POST-QUERY can be used:

BEGIN
   -- Fetch department name from another table for display
   SELECT dept_name
   INTO :EMP.DISP_DEPT_NAME
   FROM departments
   WHERE dept_id = :EMP.DEPT_ID;

   -- Format salary with currency symbol
   :EMP.DISP_SALARY := TO_CHAR(:EMP.SALARY, 'L999,999.00');
END;

Explanation:

• The department name is retrieved from another table and displayed in a non-database item (DISP_DEPT_NAME).
• The salary is formatted to include a currency symbol for better readability.
• The user sees richer information than what is directly stored in the EMP table.

Advanced Example with Security

Another scenario is applying user-specific restrictions after fetching data:

BEGIN
   -- Mask sensitive data if user does not have HR role
   IF :GLOBAL.USER_ROLE <> 'HR' THEN
      :EMP.SALARY := NULL;
      :EMP.DISP_SALARY := 'Confidential';
   END IF;
END;

In this example:

• If the user does not belong to the HR role, the salary field is hidden.
• This helps enforce security at the display level.

Best Practices for Using POST-QUERY Trigger

To make the POST-QUERY Trigger efficient and reliable, follow these best practices:

1. Keep it lightweight
   • Since it fires once per record, avoid heavy SQL queries inside it.
2. Use non-database items wisely
   • Display additional data in non-database items to avoid altering core tables.
3. Minimize database calls
   • If related data is required, consider using joins in the block’s query instead of multiple SELECTs in POST-QUERY.
4. Apply conditional logic
   • Use IF conditions to ensure logic only runs when necessary.
5. Test performance on large datasets
   • Since the trigger fires for every record, it may slow down queries with large result sets.

Common Mistakes to Avoid

Many developers misuse POST-QUERY, leading to performance or functional issues. Common mistakes include:

• Heavy SQL inside POST-QUERY: Running multiple queries for each record can slow performance drastically.
• Placing validation here: Validation should be done earlier, not after fetching data.
• Overusing non-database items: This can clutter the form and reduce maintainability.
• Relying on it for core business logic: POST-QUERY should only be used for display or enrichment, not main application logic.

Benefits of Using POST-QUERY Trigger

When used properly, the POST-QUERY Trigger provides many advantages:

• Enhances user experience by displaying more meaningful information.
• Improves data readability through formatting and calculations.
• Provides flexibility by allowing runtime logic to enrich data.
• Helps enforce security by masking sensitive values.
• Reduces dependency on complex database views by handling some logic at the form level.

Conclusion

The POST-QUERY Trigger in Oracle Forms is a powerful tool for handling data after it is retrieved but before it is shown to the user. It is ideal for tasks such as fetching related details, formatting values, applying security, and enriching the user interface with additional information.

By following best practices and avoiding common mistakes, developers can use the POST-QUERY Trigger to make forms more interactive, user-friendly, and secure. When combined with PRE-QUERY, it allows full control over both what data is fetched and how it is presented.