This discussion is archived
8 Replies Latest reply: Oct 2, 2012 12:21 AM by 962509 RSS

Unable to enforce Primary Key constraint with my code

962509 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks so much guys for all your answers, they were very helpful to me to accomplish my task....

    Thanks again so much...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points