8 Replies Latest reply: Oct 2, 2012 2:21 AM by 962509 RSS

    Unable to enforce Primary Key constraint with my code

    962509
      Hi Guys

      I have a table that contains 2 columns: code and description (defined as not null). I am using oracle forms 10g, if I inset a new row both the columns: code and description should have data e.g. code: 001 and description: Main Store...
      So on forms I have an on-update trigger on block level that handles the error messages but if the user has to update the description this trigger blocks him because the value for the code i.e. 001 is already on the database but if I remove the piece of code (the first IF block on my code together with a cursor and the entire declaration section)then the primary key constraint is violated: the code is as follows:

      declare
           
      v_store_code store_types.code%type;

      cursor store_codes is
      select a.code
      from store_types a
      where a.code = :b1.code;

      begin

      open store_codes;
      fetch store_codes into v_store_code;
      if ( store_codes%found ) then
      Message('The Store Code you have entered already exists on the Store_Types table, please enter another code!');
      Message('The Store Code you have entered already exists on the Store_Types table, please enter another code!');
      close store_codes;
      raise form_trigger_failure;
      end if;
      close store_codes;

      if :b1.code is not null and :b1.description is null then
           message('If the Store Code has been captured, then the Store Description must be captured!');
           message('If the Store Code has been captured, then the Store Description must be captured!');
      raise form_trigger_failure;
      end if;

      if :b1.description is not null and :b1.code is null then
           message('If the Store Description has been captured, then the Store code must be captured!');
           message('If the Store Description has been captured, then the Store code must be captured!');
           raise form_trigger_failure;
      end if;

      exception
                when others then
                     message(sqlerrm);
                     raise form_trigger_failure;

      end;

      My main intention here is: the user should be able to update the description field and save the changes without violating primary key on the column: code ...... please help me guys...
        • 1. Re: Unable to enforce Primary Key constraint with my code
          CraigB
          I'm confused, why are you checking for duplicates in the On-Update trigger! The fact that you are "Updating" implies that the PK value will always be in your table and therefore you will always have a duplicate.

          Rather than check the PK for duplicates, just check the description for duplicates.

          Craig...
          • 2. Re: Unable to enforce Primary Key constraint with my code
            962509
            Thanks so much for your response CraiB

            Maybe I have selected a wrong trigger or my coding is not correct, can you please help me with my coding or selecting a correct trigger because here if a user, for example, has misspelled the description while capturing and realized that later, he/she must be able to change the description and save the changes without affecting the code i.e.:

            Code(PK field) - Description(not null if code is not null i.e not null database constraint)
            001 - Main Store
            002 - Local Store
            003 - Offcrop Stroe *later realized that the spelling is incorrect and have to re-capture the description:


            Code - Description
            001 - Main Store
            002 - Local Store
            003 - Offcrop Store *now if the user is saving the changes, my code blocks him with the message on the trigger: "The Store Code you have entered already exists on the Store_Types table, please enter another code!"

            Can you please guys help me about which trigger I should use or maybe the code that I should write to accomplish this task....

            Edited by: ICM on Sep 27, 2012 2:08 AM
            • 3. Re: Unable to enforce Primary Key constraint with my code
              usman_noshahi
              ICM

              Sir as far I could understand , your problem is that your wanna allow the end users to update the Store_description , and Store_code..
              So on forms I have an on-update trigger on block level that handles the error messages but if the user has to update the description this trigger blocks him because the value for the code i.e. 001 is already on the database
              if it's right then why don't you have unique key constraints Store_description?
              alter the tables and apply unique key constraint...

              and no need to write this code
              f :b1.code is not null and :b1.description is null then
              message('If the Store Code has been captured, then the Store Description must be captured!');
              message('If the Store Code has been captured, then the Store Description must be captured!');
              raise form_trigger_failure;
              end if;
              because as you mentioned
              code and description (defined as not null)
              if they are already under not null constraints then no need to handle this in coding..
              he/she must be able to change the description and save the changes without affecting the code i.e.:
              Sir I think updation in records can only be performed when the forms is in query mode e.g execute query, have all/desired records and then make changes
              (1)description is in unique key constraint it can't be duplicated ..
              (2)code is PK it can never be duplicated...

              for this I would suggest you to have a update button on forms , when-button-pressed trigger sets property of block (update_allowed, property_true);

              and check the code of unique key violation code from oracle form's help , when that error code raise up you can show those message
              Message('The Store Code you have entered already exists on the Store_Types table, please enter another code!'); 
              Message('The Store Code you have entered already exists on the Store_Types table, please enter another code!');
              I hope it could do something for you
              thanks
              regards:
              usman noshahi
              • 4. Re: Unable to enforce Primary Key constraint with my code
                CraigB
                Keep in mind, that you are performing an UPDATE. Therefore, it is not necessary to test for a duplicate of the PK column (CODE) because you know you are going to already have a record in your table for this value. What you do want to check for is a duplicate of the DESCRIPTION. I would change your On-Update trigger CURSOR as follows:
                DECLARE
                  CURSOR store_codes is 
                    SELECT description 
                      FROM store_types
                     WHERE UPPER(description) = UPPER(:b1.DESCRIPTION);
                ...
                Notice the inclusion of the UPPER() function. I do this so that the description comparison will not miss duplicates that are different due to Upper or Lower case.

                Now you will need to change your Message to the user, because the true duplication is the description of the store not the code.
                  IF ( store_codes%found ) then 
                    Clear_Message;
                    Message('The Store Description you have entered already exists on the Store_Types table, please enter another code!'); 
                    Message('The Store Description you have entered already exists on the Store_Types table, please enter another code!'); 
                    close store_codes; 
                    ...
                Note the use of the CLEAR_MESSAGE() built-in. It is a good habit to clear the message bar before displaying a new message to ensure you don't accidently promote a status bar message to the default Alert. If there is already a message in the Message Bar, Oracle Forms will automatically promote it to an Alert when you display a new message in the Message Bar. This could cause your users to get an old message displayed in an alert and miss the new/correct message in the message bar.

                Hope this helps,
                Craig...
                • 5. Re: Unable to enforce Primary Key constraint with my code
                  user346369
                  The On-Update trigger is the wrong place to code stuff like this.

                  To enforce 'not null' fields, I always use the When-Validate-Record trigger. (But it requires a timer to put the cursor into the appropriate null column.)

                  To enforce unique keys, I let the database do that. Just go ahead and allow the form to update (or insert) the row, and use the on-error trigger to catch the error raised. You can check Error_Code and Error_Text for specific values, and if the unique key violation has occurred, issue the appropriate message. Here's an On-Error trigger code that you should be able to adapt:

                  <PRE>-- On-Error trigger (Form Level) --*
                  DECLARE
                  Err_Code NUMBER(5) := ERROR_CODE;
                  Msg VARCHAR2(150) := SUBSTR(' '||ERROR_TYPE||'-'
                  ||TO_CHAR(Err_Code)||': '||ERROR_TEXT,1,150);
                  dbms_txt VARCHAR2(200);
                  BEGIN
                  IF Err_Code IN (40508,40509) THEN
                  -- 40508=Unable to insert rec. 40509=Unable to update rec.
                  dbms_txt := SUBSTR(DBMS_ERROR_TEXT,1,200);
                  If dbms_txt LIKE '%unique constraint%'
                  And dbms_txt LIKE '%I_UK_CRED_ASSESS%' THEN
                  PLL_Err_Timer('CRED_ASSESS.CRED',
                  ' 0028 DUPLICATE RECORD IS NOT ALLOWED');
                  ELSE
                  MESSAGE(msg);
                  END IF;
                  ELSE
                  MESSAGE(msg);
                  END IF;
                  RAISE FORM_TRIGGER_FAILURE;
                  END; -- On-Error Form Level trigger --</PRE>

                  PLL_Err_Timer is just my PLL library procedure that uses a timer to put the cursor in the appropriate column, and issues the message.
                  • 6. Re: Unable to enforce Primary Key constraint with my code
                    Andreas Weiden
                    I agree with Steve that the ON-UPDATE is the wrong place for your code.

                    I would put it in the WHEN-VALIDATE-RECORD and, additionally, in the PRE-UPDATE. And for the unique key-check..., you have to make sure that you exclude the current record in your comparison,
                    so change
                    cursor store_codes is
                    select a.code
                    from store_types a
                    where a.code = :b1.code;
                    to
                    cursor store_codes is
                    select a.code
                    from store_types a
                    where a.code = :b1.code
                      AND (   (    A.ROWID!=:B1.ROWID
                               AND :B1.ROWID IS NOT NULL
                              )
                           OR :B1.ROWID IS NULL
                          )
                    ;
                    or, make the code-field non-updateable, then you do not have to check it when updating.
                    • 7. Re: Unable to enforce Primary Key constraint with my code
                      962509
                      Thanks Andreas, that's what I needed to do, to exclude the current record in my comparison and also to change the trigger, your feedback was very helpful...
                      (I wanted to mark your answer as helpful and correct but after clicking helpful I didn't notice that I won't see the button: correct)
                      Thanks so much....

                      Edited by: ICM on Oct 2, 2012 12:14 AM
                      • 8. Re: Unable to enforce Primary Key constraint with my code
                        962509
                        Thanks so much guys for all your answers, they were very helpful to me to accomplish my task....

                        Thanks again so much...