The following example finds the commission plan in the COMMPLAN table, based on the current value of the commcode item in the EMPLOYEE block in the form, to verify that the code is valid.
If the code in the COMMPLAN table is located, the description of the COMMPLAN is obtained and deposited in the non-database Description item. Otherwise, an error is raised.
If the code in the COMMPLAN table is located, the description of the COMMPLAN is obtained and deposited in the non-database Description item. Otherwise, an error is raised.
/* ** Method 1: Using a SELECT...INTO statement, the trigger ** looks more readable but can be less efficient ** than Method 2 because for ANSI Standard ** compliance, the SELECT...INTO statement must ** return an error if more than one row is ** retrieved that matches the criteria. This ** implies PL/SQL may attempt to fetch data twice ** from the table in question to insure that there ** aren’t two matching rows. */ BEGIN SELECT description INTO :Employee.Commplan_Desc FROM commplan WHERE commcode = :Employee.Commcode; EXCEPTION WHEN No.Data_Found THEN Message(’Invalid Commission Plan, Use <List> for help’); RAISE Form_trigger_Failure; WHEN Too_Many_Rows THEN Message(’Error. Duplicate entries in COMMPLAN table!’); RAISE Form_trigger_Failure; END; /* ** Method 2: Using an Explicit Cursor looks a bit more ** daunting but is actually quite simple. The ** SELECT statement is declared as a named cursor ** in the DECLARE section and then is OPENed, ** FETCHed, and CLOSEd in the code explicitly ** (hence the name). Here we guarantee that only a ** single FETCH will be performed against the ** database. */ DECLARE noneFound BOOLEAN; CURSOR cp IS SELECT description FROM commplan WHERE commcode = :Employee.Commcode; BEGIN OPEN cp; FETCH cp INTO :Employee.Commplan_Desc; noneFound := cp%NOTFOUND; CLOSE cp; IF noneFound THEN Message(’Invalid Commission Plan, Use <List> for help’); RAISE Form_trigger_Failure; END IF; END;