/*
** 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;