[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1152
  • Last Modified:

Trigger for forms

I have table test(a varchar2(9));

The values are of the form

26-00-000
26-01-001
26-01-002
26-02-001
26-02-002
26-02-003
26-01-003


You can understand it as level wise:
26-00-000
   26-01-001
   26-01-002
   26-01-003
 
   26-02-001
   26-02-002
   26-02-003

For insertion through form I want some restrictions.

If user enter a value in the form, it's all previous levels must exists otherwise this insetion should terminate.

Look if user insert 26-01-004 or 26-02-004 these are accurate as there previous levels are present. But if the user enter 26-01-010, this entry should not inserted into database as there are now gaps between the third levels. Similarly if user enter 27-01-001, This should also terminate the entery as there should be 27-00-000 inserted before inserting 27-01-001. Can you help me here to how to write a trigger in forms

Thanks in advance

0
hyynes
Asked:
hyynes
  • 2
1 Solution
 
sapnamCommented:
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
0
 
hyynesAuthor Commented:
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
0
 
sapnamCommented:
The code I had posted was as under

BEGIN
  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;
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

IF you are getting a compilation error on this, there can be 2 reasons :

1. In one of the codes , i have given an extra comma
  substr(:your_column,,7,3) <> '000'
  should read as
  substr(:your_column,7,3) <> '000'
2. I have begun an IF and then given the second condition with an ELSIF.  There will be some more conditions. So I have not given the END IF;
In the above code, you will have to give an
END IF;
END;

at the end.

If you still have a problem, post your code here and I will check the same
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now