Understanding “When Validate Item” and “When Validate Record” in Oracle Forms

Oracle Forms is a powerful tool for building data entry applications with a graphical user interface. One of its key features is the use of triggers, which allow developers to control application behavior based on user interactions.

Two important validation triggers in Oracle Forms are:

• “When Validate Item” (WVI) – Validates data at the field (item) level before the user moves to another field.
• “When Validate Record” (WVR) – Validates the entire record before saving or navigating away from it.

Although both triggers ensure data integrity, they have different scopes and execution timings. This article explains their differences, use cases, and best practices. 

What Is 1. When Validate Item in Oracle Forms

1. What is “When Validate Item”?

The When Validate Item (WVI) trigger fires when the user modifies a field and then tries to leave it. If the user does not change the field’s value, the trigger does not fire even if they navigate away.

Use Cases for “When Validate Item”

✅ Data Formatting – Ensuring that input follows a specific format (e.g., validating email or phone number formats).
✅ Value Constraints – Checking that numeric values fall within an acceptable range (e.g., a discount cannot exceed the total price).
✅ Business Rule Enforcement – Preventing users from entering logically incorrect values (e.g., a start date cannot be after an end date).

Example: Preventing past dates in an “order_date” field

IF :order_date < TRUNC(SYSDATE) THEN
   MESSAGE('The order date cannot be in the past.');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

💡 Why this works: TRUNC(SYSDATE) ensures that time values are ignored, so only the date is considered.

2. When Validate Record in Oracle Forms

What is “When Validate Record”?

The When Validate Record (WVR) trigger fires when the user tries to leave a record or save it. It ensures that all fields within the record meet validation rules before committing.

Execution Order:

1 WVI triggers run first for any modified fields.
2️ WVR runs after all WVI triggers to validate the complete record.

Use Cases for “When Validate Record”

✅ Cross-Field Validation – Ensuring relationships between fields are valid (e.g., the total price must equal quantity × unit price).
✅ Referential Integrity – Checking if a record conflicts with existing data before saving.
✅ Transaction Validations – Ensuring all required fields are filled before committing data.

Example: Preventing negative quantities before saving

IF :quantity < 0 THEN
   MESSAGE('Quantity cannot be negative!');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

💡 Why this works: The system will block the record from being saved if an invalid quantity is detected.

3. Key Differences Between “When Validate Item” and “When Validate Record”

Feature	When Validate Item (WVI)	When Validate Record (WVR)
Scope	Single field (item)	Entire record
Trigger Timing	When user leaves the field (if value changed)	Before committing or leaving the record
Common Use Cases	Checking field constraints (e.g., date range, numeric limits)	Ensuring logical consistency across fields (e.g., total calculations)
Dependency	Fires only if the field value changes	Fires after all WVI triggers in the record

4. Best Practices for Using These Triggers

✅ Use WVI for individual field validation and WVR for validating relationships between fields.
✅ Avoid redundant checks – If a validation applies to multiple fields at once, it’s better placed in WVR.
✅ Use clear error messages so users understand why their input is invalid.
✅ Test dependencies between fields – If an item’s value depends on another, ensure validations fire in the correct sequence.

Conclusion

Both “When Validate Item” (WVI) and “When Validate Record” (WVR) are essential for ensuring data accuracy in Oracle Forms. WVI is used for field-level validation, while WVR ensures that entire records meet business rules before saving. Understanding how and when these triggers fire allows developers to create more reliable and efficient forms while preventing data entry errors.

By using these triggers correctly, you can enforce business logic, enhance data integrity, and improve user experience in Oracle Forms applications. 🚀

🔗 Learn more at morphis-tech.com