Oracle Forms Tutorials: WHEN-VALIDATE-ITEM trigger
Consider that you have an oracle form on which there is a datablock which uses EMP table. EMP table has a column called SALARY. Now there is a contraint on the SALARY column that it should be greater than or equal to $1000. Your requirement is that whenever any user fills salary in the Oracle Forms ITEM (say ITEM_SALARY) and tabs out from that item, a validation should fire and if the value filled is not valid, it should give you an error message and does not let the cursor go to the other item. In these situations WHEN-VALIDATE-ITEM trigger is used. Following is the PLSQL code you should write on the WHEN-VALIDATE-ITEM trigger of the ITEM_SALARY item.
IF :ITEM_SALARY < 1000 THEN
MESSAGE('ERROR: Salary must be at least $1000 or more.');
RAISE FORM_TRIGGER_FAILURE; -- To keep the cursor in the item
You should also go through this video on YOUTUBE by Edward Honour.
In this video, he tries to pick the department name when the user enters the department number. If department number does not exist in the database, he shows the error message and does not let the cursor to go to the other item using FORM_TRIGGER_FAILURE trigger. He has used NO_DATA_FOUND exception for showing the error message. Following is the code used in this video:
SELECT DEPT_NAME INTO :BLOCKNAME.ITEMNAME FROM DEPT WHERE DEPT_NO = :BLOCKNAME.ITEMNAME2;
WHEN NO_DATA_FOUND THEN
MESSAGE('Invalid Department Number');