5 Replies Latest reply: Aug 14, 2013 1:05 AM by Elad RSS

    Duplicate records when inserting

    Elad

      Hi,

      I encounter with a problem when I try to save a new record that I just insert.

      Oracle forms is saving all the records in the block, including the old records (which are exist in the block and in the DB).

      According to the answers in the forum, the reason is because I use cursor to populate my data-block. but, I need to use cursor, since I retrieve from 2 tables and I do manipulate on anther column that isn't depend on column from the DB.

      which trigger I have to use, I more important - What the code I have to write in it?

       

      Thanks in advanced,

      Elad


        • 1. Re: Duplicate records when inserting
          Soofi

          need more details..

           

          And if you are going to restrict duplicate values which is in DB then go for PRIMARY KEY or

           

          If you are taking values from two different tables then

           

          IN WHEN VALIDATE RECORD TRIGGER of the data block write the code like this

           

           

           

          DECLARE

           

           

          CURSOR C1 IS

          SELECT * FROM TABLENAME1;

           

           

          CURSOR C2 IS

          SELECT * FROM TABLENAME2;

           

           

          CR1 C1%ROWTYPE;

          CR2 C2%ROWTYPE;

           

           

          VARIABLE NUMBER(5);

           

           

          BEGIN

           

           

           

           

          --YOUR CONDITIONS--

           

           

          END;

          • 2. Re: Duplicate records when inserting
            O.Developer

            HI

             

            Though  your  details , not able to get it  exactly.  But here is  is code for checking duplication --  in  EMp table.

            This code will check for duplication while entering employee number.

             

            The filed  is EMPNO.

            Block Name is :EMP

            Trigger Name : When-Validate-ITem  ( enter the below code.)

             

            Declare

            Ex_CTs Number;

            Begin

             

            Select   count(*) into Ex_Cts from emp where empno =:emp.empno;

            If Ex_Cts >0  Then

            Message('Employee Alreday exists -Duplicate Not allowed');

            Raise Form_Trigger_Failure;

            End If;


            Exception

            When NO Data Found Then  Null;

            When Others  Then

            Message('Other  Exception Error');

            End;


            -----------

             

            In case , you are not clear,  post here your table  structure and populating codes

            • 3. Re: Duplicate records when inserting
              Elad

              Hi,

              Thank-you for your answers, guys.

              I will try to give more details.

              I populate the data-block in WHEN-NEW-BLOCK-INSTANCE trigger in block level.

              here is the code of this trigger:

               

              DECLARE


              v_record_counter  NUMBER   ;

              CURSOR my_cur  IS
                  SELECT
                     T1.EMP_REF_ID       ,
                     E.EMPLOYEE_NAME         ,
                     T1.LAST_UPDATED        ,
                     T1.LAST_UPDATED_BY       ,
                     E.EMAIL
                 FROM
                        TABLE1  T1 , EMPLOYEES E
                WHERE
                        T1.EMPLOYEE_REF_ID = E.EMPLOYEE_ID;
               
              BEGIN
                --IF :GLOBAL.v_count < 1 THEN --populate the block just once, when new-form-instance
                  v_record_counter := 0 ;
                 FOR my_rec IN my_cur
                 LOOP
                  :MY_DATA_BLOCK.EMPLOYEE_REF_ID       := my_cur.EMP_REF_ID       ;
                  :MY_DATA_BLOCK.EMPLOYEE_NAME         := my_cur.EMPLOYEE_NAME        ;  
                  :MY_DATA_BLOCK.LAST_UPDATED             := my_cur.LAST_UPDATED         ;
                  :MY_DATA_BLOCK.LAST_UPDATED_BY      := my_cur.LAST_UPDATED_BY       ; 
                 
                  v_record_counter := v_record_counter + 1 ;
                 
                  IF my_cur.EMAIL IS NULL  THEN
                   :MY_DATA_BLOCK.IS_EMAIL := 'N';
                  ELSE
                    :MY_DATA_BLOCK.IS_EMAIL := 'Y';
                  END IF;
                     
                  NEXT_RECORD;
                 
                 END LOOP;
                
                -- :GLOBAL.v_count := :GLOBAL.v_count+1;

                --END IF;
              END;

               

              if I insert a new record in this point, all the records are saved in the DB (including the old records).

              so, I wrote code in ON-INSERT trigger so that to save only the insert records:

               

              DECLARE

              v_record_status VARCHAR2(20);
              v_first_Rec number;
              v_last_Rec number;
               
              BEGIN
              --GO_BLOCK(MY_DATA_BLOCK);

              --LAST_RECORD;
              v_last_Rec :=  *******************how to declare the last_record????***************

              --First_RECORD;
                v_first_Rec:=**************** how to declare the first_record????******************

              FOR i in v_first_Rec..v_last_Rec
              LOOP
                v_record_status := GET_RECORD_PROPERTY( I, MY_DATA_BLOCK , Status);
                IF v_record_status = 'CHANGED' OR  v_record_status = 'INSERT' OR v_record_status = 'NEW' THEN
                

              *************  here I need to write the code which save only the new records******************
                END IF;
                exit when :system.last_record = 'TRUE';
              END LOOP;
              END;

               

              Thanks in advanced,

              Elad

              • 4. Re: Duplicate records when inserting
                XeM

                Hi,

                 

                What about VIEW. Create a view which selects all the fields you need from both tables and then base your block on that view. For insertion, update and delete of records use instead of trigger at database level and apply your logic.

                • 5. Re: Duplicate records when inserting
                  Elad

                  Hi Xem , Soofi and O.Developer

                  Eventually, the solution is  summed up by a single line in the cursor...

                  Just before "next record" we need to write this function:

                  SET_RECORD_PROPERTY(my_cursor%ROWCOUNT , MY_DATA_BLOCK, STATUS , QUERY_STATUS);

                   

                  It's assign the status "QUERY_STATUS" to each record that retrieve from the DB (In the first time, when-new-block-instance), and like this, when we commit inserting - these records not anymore considered as "new" ones...

                   

                  remark: Don't need the 'on-insert' trigger.

                   

                  Elad