How to write WHEN_VALIDATE trigger in Oracle Forms.

WHEN_VALIDATE_RECORD and WHEN_VALIDATE_ITEM is 2 most useful trigger used in Oracle Forms. It able to restrict user to entering data invalid to business logic. Developer can implement business logic into when validate trigger. Here is the step how to write when validate trigger.

As you can see the picture above, right click on the desired item and select PL/SQL editor.

I am using the name of the block and item as :block1.item1

Here is the trigger code. It will check for item cannot be blank and item value should be greater than 0.

if :block1.item1 is null then
   alrt_msg('Item data cannot be blank');
   raise form_trigger_failure;
end if;

if :block1.item1<=0  then
   alrt_msg('Item value should be greater than zero');
   raise form_trigger_failure;
end if;

raise form_trigger_failure is actually restrict user from skipping the validation.

WHEN_VALIDATE_RECORD

 when_validate_record trigger check business logic in record level. This trigger is required when you need record level validation.

Don't forget to add this trigger at block level.

Suppose there is an entry form consisting of emp_code, pay, incentive.
Now if someone basic is zero or blank then 

This is the when_validate_record plsql code.

if nvl(:block1.pay,0)=0 and nvl(:block1.incentive,0)>0 then
   alrt_msg('Incentive cannot be greater than zero when basic is blank');
   raise form_trigger_failure;
end if;

We have used nvl to return default value if item is null.

You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=_SbYRyF48dc

 Press like if you like the video and you can also subscribe to my channel : https://www.youtube.com/subhro190776