Oracle Forms Triggers: A Complete Reference Guide

Triggers are the heart of Oracle Forms programming. Every meaningful behavior in a Forms application — validation, navigation control, data manipulation, calculations — is implemented through triggers. Understanding how triggers work, their firing order, and when to use each type is fundamental to building reliable Oracle Forms applications.

This guide covers the major trigger categories, key trigger properties, firing order, and practical examples of the triggers you will use most in real applications.

What Is a Trigger in Oracle Forms?

A trigger is a block of PL/SQL code that executes automatically when a specific event occurs — a key press, a field value change, a record being committed, or a query being executed. Triggers are attached to a specific scope level in the form hierarchy:

• Form-level triggers: Apply to the entire form, fire for any block/item
• Block-level triggers: Apply to all items within a specific block
• Item-level triggers: Apply to a specific item only

When the same trigger type exists at multiple levels (e.g., WHEN-VALIDATE-ITEM at both block and item level), Oracle Forms fires them in order from most specific to most general (item → block → form). You can override this with the Fire in Enter Query Mode and Execution Hierarchy properties.

Execution Hierarchy: Override vs Before vs After

The Execution Hierarchy property controls how a trigger interacts with the same trigger type at higher scope levels:

• Override (default): Only this trigger fires; triggers at higher levels are ignored
• Before: This trigger fires first, then the trigger at the next higher level
• After: Triggers at higher levels fire first, then this trigger runs

Use Before/After to layer trigger logic across scope levels without duplicating code.

Key Trigger Categories

Validation Triggers

WHEN-VALIDATE-ITEM — Fires when the user attempts to leave an item that has been modified. Use this for item-level business rule validation:

-- WHEN-VALIDATE-ITEM on P_SALARY
IF :P_SALARY < 0 THEN
  MESSAGE('Salary cannot be negative.');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

IF :P_SALARY > 500000 THEN
  MESSAGE('Salary exceeds the maximum allowed value. Please verify.');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

WHEN-VALIDATE-RECORD — Fires when the user leaves a record (moves to next record or commits). Use for cross-item validation — business rules that involve multiple fields:

-- WHEN-VALIDATE-RECORD on ORDERS block
IF :ORDERS.END_DATE IS NOT NULL AND :ORDERS.END_DATE < :ORDERS.START_DATE THEN
  MESSAGE('End date must be on or after the start date.');
  GO_ITEM('ORDERS.END_DATE');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

Navigation Triggers

PRE-TEXT-ITEM / POST-TEXT-ITEM — Fire just before and just after the cursor enters/leaves a text item. Useful for field-level setup and cleanup:

-- PRE-TEXT-ITEM: highlight the field when entering
SET_ITEM_PROPERTY('ORDERS.ORDER_DATE', VISUAL_ATTRIBUTE, 'VA_ACTIVE_FIELD');

-- POST-TEXT-ITEM: remove highlight when leaving
SET_ITEM_PROPERTY('ORDERS.ORDER_DATE', VISUAL_ATTRIBUTE, 'VA_NORMAL_FIELD');

WHEN-NEW-ITEM-INSTANCE — Fires whenever the cursor moves to a new item (any item). This is the most commonly used trigger for responding to focus changes. It fires even when navigating via Tab, click, or Go_Item.

WHEN-NEW-RECORD-INSTANCE — Fires whenever the cursor moves to a new record. Use this to initialize calculated fields or set defaults based on the new record's position:

-- WHEN-NEW-RECORD-INSTANCE: set sequence-based default for new records
IF :SYSTEM.RECORD_STATUS = 'NEW' THEN
  :ORDER_LINES.LINE_NUMBER := :SYSTEM.TRIGGER_RECORD;
END IF;

Query Triggers

PRE-QUERY — Fires just before Oracle Forms sends the query to the database. Use this to add dynamic WHERE conditions or validate search parameters:

-- PRE-QUERY: force a filter to prevent unbounded queries
IF :EMP_BLOCK.DEPARTMENT_ID IS NULL 
   AND :EMP_BLOCK.LAST_NAME IS NULL 
THEN
  MESSAGE('Please enter at least one search criterion before querying.');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

POST-QUERY — Fires after each row is fetched from the database (once per row). Use to populate non-database items or calculated fields for each fetched record:

-- POST-QUERY: calculate a display-only field for each fetched record
:ORDERS.TOTAL_WITH_TAX := :ORDERS.ORDER_TOTAL * 1.15;

-- Look up a description for a code field
SELECT status_desc INTO :ORDERS.STATUS_DESCRIPTION
FROM status_codes
WHERE status_code = :ORDERS.STATUS;

Transaction Triggers

PRE-INSERT / PRE-UPDATE / PRE-DELETE — Fire just before Oracle Forms sends INSERT, UPDATE, or DELETE to the database. Use for final validation or to set audit fields:

-- PRE-INSERT: set audit fields automatically
:ORDERS.CREATED_BY := USER;
:ORDERS.CREATED_DATE := SYSDATE;
:ORDERS.ORDER_ID := ORDER_SEQ.NEXTVAL;

-- PRE-UPDATE: set last modified audit fields
:ORDERS.LAST_MODIFIED_BY   := USER;
:ORDERS.LAST_MODIFIED_DATE := SYSDATE;

POST-INSERT / POST-UPDATE / POST-DELETE — Fire after the DML succeeds but before the commit. Use for cascading operations or logging:

-- POST-INSERT: log the new order to an audit table
INSERT INTO order_audit (order_id, action, action_by, action_date)
VALUES (:ORDERS.ORDER_ID, 'INSERT', USER, SYSDATE);

Key Triggers

Key triggers intercept specific keystrokes and replace or supplement their default behavior:

• KEY-COMMIT (F10): Override the standard save/commit behavior
• KEY-QUERY (F7/F8): Control how query mode is entered or executed
• KEY-LISTVAL (F9): Fire when the user presses the LOV key on a field
• KEY-NEXT-ITEM (Tab): Control Tab key navigation
• KEY-EXIT (Ctrl+Q): Override the form exit/close behavior

-- KEY-COMMIT: confirm before saving
IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
  IF SHOW_ALERT('CONFIRM_SAVE') = ALERT_BUTTON1 THEN
    COMMIT_FORM;
  END IF;
ELSE
  MESSAGE('No changes to save.');
END IF;

WHEN-BUTTON-PRESSED

The most straightforward trigger — fires when a user clicks a button. All button-driven logic lives here:

-- WHEN-BUTTON-PRESSED on SEARCH button
SET_BLOCK_PROPERTY('EMP_BLOCK', DEFAULT_WHERE, 
  'department_id = ' || :SEARCH_BLOCK.DEPT_ID);
EXECUTE_QUERY;

FORM_TRIGGER_FAILURE: Stopping Navigation

RAISE FORM_TRIGGER_FAILURE is the mechanism for stopping the current operation in Oracle Forms. When raised inside a validation trigger, it prevents the cursor from leaving the current item. When raised inside a PRE-INSERT trigger, it prevents the insert from happening.

Always display a MESSAGE before raising FORM_TRIGGER_FAILURE so users know why they are stuck.

Common Mistake: Trying to Modify Items in PRE-QUERY

Items in a block that has query mode active cannot be modified through normal assignment in most triggers. If you try to set an item's value in PRE-QUERY when the user is in Enter Query Mode, you will get an error. Use :BLOCK.ITEM := value carefully and check :SYSTEM.MODE when needed:

IF :SYSTEM.MODE = 'NORMAL' THEN
  -- Safe to modify item values
  :EMP_BLOCK.DEPT_NAME := get_dept_name(:EMP_BLOCK.DEPT_ID);
END IF;

Summary: Quick Trigger Reference

Trigger	When It Fires	Common Use
WHEN-VALIDATE-ITEM	On leaving modified item	Item-level validation
WHEN-VALIDATE-RECORD	On leaving modified record	Cross-item validation
WHEN-NEW-RECORD-INSTANCE	Cursor moves to new record	Set defaults, init calculated fields
WHEN-NEW-ITEM-INSTANCE	Cursor moves to any item	Respond to focus changes
PRE-QUERY	Before database query	Add WHERE conditions, validate search
POST-QUERY	After each row fetched	Populate non-DB items
PRE-INSERT	Before INSERT sent to DB	Set audit fields, generate keys
PRE-UPDATE	Before UPDATE sent to DB	Set audit fields, final validation
WHEN-BUTTON-PRESSED	User clicks a button	All button-driven logic
KEY-COMMIT	User presses save key	Custom save logic, confirmation

Conclusion

Mastering Oracle Forms triggers means mastering the event-driven programming model that makes Forms applications respond intelligently to user actions. The triggers listed in this guide cover the vast majority of real-world scenarios — from field validation to query control, audit trail maintenance, and custom navigation.

When starting with a new Forms application, always identify which triggers are appropriate for each piece of logic before writing code. Choosing the right trigger level and scope is as important as the code itself — a validation in the wrong trigger will fire at the wrong time and create confusing behavior.