How to use the “WHEN-VALIDATE-ITEM” trigger?

Hands-On problem

===================

Your client has a form that contains an item that holds a value for the “COMM-PAID-YTD” column (Commission Paid Year To Date). They want to validate the data in that column to be between 200 and 800 dollars. Anything else, it must give the following error messages:

If the commission is less than 200 then a message should be displayed as:

                ERROR: Commission must be at least $200.00 or more.

If the commission is more than 800 then a message should be:

                ERROR: Year to date commission can not exceed $800.00.

 You as a programmer, asked to write a trigger to check the field item and send an error message to a user if the data entered was violated by above rules.

Gathered By: John Kazerooni

Solution:

=========

Assuming that you have a form that created by the “CUSTOMERS” table and there is an item called “COMM_PAID_YTD.”

1- Go to the object navigator window and expand the “COMM_PAID_YTD” item.

2- Highlight the “Trigger” item and click on (+) the “Create” icon.

3- Select the “WHEN-VALIDATE-ITEM” trigger.

3- Write the following procedure: 

IF :comm_paid_ytd < 200 THEN

    -- Must be at least $200.

    message('ERROR: Commission must be at least $200.00 or more.');

    display_error;  -- Display the message box

    raise form_trigger_failure; -- to keep the cursor in the item

ELSIF :comm_paid_ytd > 800 THEN

    -- Not more than $800.

    message('ERROR: Year to date commission can not exceed $800.00.');

    display_error; -- Display the message box

    raise form_trigger_failure;          -- to keep the cursor in the item

END IF;

4- Compile and close the trigger.

5- Run the form, go to the “COMM-PAID-YTD” item, and enter an invalid value such as 900 or 50.