Trigger for forms

You will be having a block in your forms in which you will be displaying these records.  For every new record entered, you want to check whether it meets specified criteria. The validation that you perform should be against the tables existing data as well as against the existing records in the block (as they may not yet have been committed).

So you can have a Key-Next-Item trigger on the field where the code is entered.  The code will be as under :

BEGIN
  -- first check whether code entered already exists in the table
      DECLARE
          rec_exists  VARCHAR2(1) := 'N';
      BEGIN
          SELECT 'Y'
              INTO rec_exists
              FROM your_table
            WHERE your_colum = :your_column;
          MESSAGE('Code Already Entered');
         RAISE FORM_TRIGGER_FAILURE;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
            NULL;
     END;
     -- then check whether code entered has been repeated in the block.  
      check_duplicate_record(:your_column,
                                          :system.cursor_record);
     -- then check whether code entered matches specified criteria
     BEGIN
         -- if 4th and 5th digits are 00 then 7th 8th 9th should be 000.
         IF susbtr(:your_column,4,2) = '00' THEN
             IF substr(:your_column,,7,3) <> '000' THEN
                  MESSAGE('Incorrect code');
                  RAISE FORM_TRIGGER_FAILURE;
             END IF;
        -- if 4th and 5th digits are 01,02 etc, then 00 record must be there either in table or in block
        ELSIF substr(:your_column,4,2) <> '00' THEN
             DECLARE
                 rec_exists VARCHAR2(1) := 'N';
             BEGIN
                SELECT 'Y'
                   INTO rec_exists
                   FROM your_table
                 WHERE your_colum = substr(:your_coumn,1,2)||'-00-000';
             EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                      check_in_block(substr(:your_coumn,1,2)||'-00-000',:system.cursor_record);
            END;
and so on you can add other ELSE conditions for the other checks you want to perform.

Check_duplicate_record will be a program unit as under
PROCEDURE check_duplicate_record(value_to_check IN VARCHAR2, curr_rec IN VARCHAR2) IS
FIRST_RECORD;
   LOOP
      IF :your_column = value_to_check and :system.cursor_record <> curr_rec THEN
          MESSAGE('Record already entered');
          RAISE FORM_TRIGGER_FAILURE;
     END IF;
     IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
         EXIT;
     ELSE
         NEXT_RECORD;
    END IF;
  END LOOP;
  go_record(curr_rec);
END;

Check_in_block will be a program unit as under
PROCEDURE check_in_block(value_to_check IN VARCHAR2, curr_rec IN VARCHAR2) IS
rec_found NUMBER := 0;
FIRST_RECORD;
   LOOP
      IF :your_column = value_to_check  THEN
           rec_found := rec_found+1;
     END IF;
     IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
         EXIT;
     ELSE
         NEXT_RECORD;
    END IF;
  END LOOP;
  IF rec_found = 0 THEN
      MESSAGE('Parent record not found');
      RAISE FORM_TRIGGER_FAILURE;
  ELSIF rec_found > 1 THEN
      MESSAGE('Multiple Parent records found');
      RAISE FORM_TRIGGER_FAILURE;
  ELSE
     GO_RECORD(curr_rec);
  END IF;
END;

Hope this helps

Hi Sapnam, Your first trigger does not compiled correct. Please review it. I have changed the later two and they working well but not first. Problems look over IF statement.

Sorry for bothering, But i tried my level best and changed all the column_names according to your comments but does not compiled successfully.

Thanks for help