This content has been marked as final. Show 8 replies
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.
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
Sir as far I could understand , your problem is that your wanna allow the end users to update the Store_description , and Store_code..
if it's right then why don't you have unique key constraints Store_description?
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
alter the tables and apply unique key constraint...
and no need to write this code
because as you mentioned
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;
if they are already under not null constraints then no need to handle this in coding..
code and description (defined as not null)
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
he/she must be able to change the description and save the changes without affecting the code i.e.:
(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
I hope it could do something for you
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!');
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:
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.
DECLARE CURSOR store_codes is SELECT description FROM store_types WHERE UPPER(description) = UPPER(:b1.DESCRIPTION); ...
Now you will need to change your Message to the user, because the true duplication is the description of the store not the code.
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.
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; ...
Hope this helps,
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) --*
Err_Code NUMBER(5) := ERROR_CODE;
Msg VARCHAR2(150) := SUBSTR(' '||ERROR_TYPE||'-'
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
' 0028 DUPLICATE RECORD IS NOT ALLOWED');
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.
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,
cursor store_codes is select a.code from store_types a where a.code = :b1.code;
or, make the code-field non-updateable, then you do not have to check it when updating.
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 ) ;
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