Home » Developer & Programmer » Forms » duplicate record is not working ?? (Oracle 10g)
duplicate record is not working ?? [message #426962] Tue, 20 October 2009 07:17 Go to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
i have 2 database block, detail block has ID field and this is the trigger(when_validate_item) in ID Field :
:s_name := null;

if :id is not null
then
   begin
   select spriden_pidm,
          substr(ltrim(rtrim(spriden_first_name||' '||spriden_mi)||' ')||spriden_last_name,1,40)
   into   :PIDM,
          :s_name
   FROM   SPRIDEN
   where  spriden_id = :id
   and    spriden_change_ind is null;

   exception when no_data_found then
          message('*ERROR* Invalid ID; press LIST for Name/ID search.');
          RAISE FORM_TRIGGER_FAILURE; 
   end;

if :pidm != :PYPSTSD_PARTICIPANT_PIDM then :PYPSTSD_PARTICIPANT_PIDM := :pidm;
end if;

end if;



begin
SELECT distinct 1 into :global.dummy
FROM   PYPSTSD
WHERE  PYPSTSD_PARTICIPANT_PIDM = :PIDM
and    PYPSTSD_DATE             = :PYPSTSD_DATE
and    PYPSTSD_TRAINING_LOCN    = :PYPSTSD_TRAINING_LOCN
and    PYPSTSD_TRAINING_CODE    = :PYPSTSD_TRAINING_CODE;

message('*ERROR* Duplicated Record.');
RAISE FORM_TRIGGER_FAILURE; 

exception when no_data_found then null;
end;



and i have trigger on detail block (post_query):

begin
select spriden_id,
       substr(ltrim(rtrim(spriden_first_name||' '||spriden_mi)||' ')||spriden_last_name,1,40)
into   :id,
       :s_name
FROM   SPRIDEN
where  spriden_pidm = :PYPSTSD_PARTICIPANT_PIDM
and    spriden_change_ind is null;

exception when no_data_found then null;
end;

still the duplicate record is not working ??? i don't know why ?? ... i appreciate if you can look at this problem.

[EDITED by LF: applied [code] tags]

[Updated on: Tue, 20 October 2009 15:50] by Moderator

Report message to a moderator

Re: duplicate record is not working ?? [message #426967 is a reply to message #426962] Tue, 20 October 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
First of all, when posting code can you please use code tags, it makes it a lot easier to read - see the orafaq forum guide if you're not sure how.

Secondly, what exactly do you think this bit of code should be doing that it isn't?
Re: duplicate record is not working ?? [message #426977 is a reply to message #426967] Tue, 20 October 2009 08:06 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
sorry ... i didn't know that there is code tags, that is good to know, and i just want to give msg to the user that can not write same id when he/she duplicate the ID.
Re: duplicate record is not working ?? [message #426980 is a reply to message #426962] Tue, 20 October 2009 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
That code doesn't appear to do that?

The query that checks the id isn't the one that's used for the duplicate message.
Re: duplicate record is not working ?? [message #426984 is a reply to message #426980] Tue, 20 October 2009 08:31 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
ID field is non-database item ...PYPSTSD_PARTICIPANT_PIDM field is the primay key in the detail block.
when the user write their ID, it goes to PIDM field and i copy this
one to PYPSTSD_PARTICIPANT_PIDM so when the use write their ID... can get their Name(display item).
Re: duplicate record is not working ?? [message #426987 is a reply to message #426962] Tue, 20 October 2009 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use messages to check that the datablock items in this select:
SELECT distinct 1 into :global.dummy
FROM PYPSTSD
WHERE PYPSTSD_PARTICIPANT_PIDM = :PIDM
and PYPSTSD_DATE = :PYPSTSD_DATE
and PYPSTSD_TRAINING_LOCN = :PYPSTSD_TRAINING_LOCN
and PYPSTSD_TRAINING_CODE = :PYPSTSD_TRAINING_CODE;

Have the values you think they have.
Also why are you using a global variable instead of a local variable here?
Re: duplicate record is not working ?? [message #426990 is a reply to message #426987] Tue, 20 October 2009 08:55 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
YES... they have value, it's comes from master block, they are primary-foreign key
Re: duplicate record is not working ?? [message #426992 is a reply to message #426990] Tue, 20 October 2009 08:58 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
is there any attachment that i can attach my .fmb , or this is my e-mail to send me your e-mail so i can attach the .fmb and send it to you.
Re: duplicate record is not working ?? [message #426993 is a reply to message #426992] Tue, 20 October 2009 08:58 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
my e-mail is laith@aus.edu
Re: duplicate record is not working ?? [message #426996 is a reply to message #426962] Tue, 20 October 2009 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
This bit of code:
begin
SELECT distinct 1 into :global.dummy
FROM PYPSTSD
WHERE PYPSTSD_PARTICIPANT_PIDM = :PIDM
and PYPSTSD_DATE = :PYPSTSD_DATE
and PYPSTSD_TRAINING_LOCN = :PYPSTSD_TRAINING_LOCN
and PYPSTSD_TRAINING_CODE = :PYPSTSD_TRAINING_CODE;

message('*ERROR* Duplicated Record.');
RAISE FORM_TRIGGER_FAILURE; 

exception when no_data_found then null;
end;

Will give the message if the select finds anything.
So one of these must be the case:
1) The values in the datablock items aren't what you think they are.
2) The values in the Pypstsd table aren't what you think they are.
3) The select isn't being run at.

I can't debug this for you.
So use messages to check the values of the datablock items and to check that that bit of code is being run.
Then run that query in sqlplus using the values you got from the messages to check that the record you think exists in PYPSTSD actually does.

If you're still stuck after that post what you found out and we'll go from there.

You can attach you form to this thread but I doubt that'll be much use at this point.
Re: duplicate record is not working ?? [message #427001 is a reply to message #426996] Tue, 20 October 2009 09:36 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
( I showed both of filed ID & PYPSTSD_PARTICIPANT_PIDM in my screen) and there is value showed in PIDM field and nothing showed in PYPSTSD_PARTICIPANT_PIDM field. now i wan to know how to write this code that when the users write their duplicate ID in the ID field ...showing them message... now the message is not showing.
Re: duplicate record is not working ?? [message #427005 is a reply to message #426962] Tue, 20 October 2009 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you check the select was being run?
Did you run the select in sqlplus?
Re: duplicate record is not working ?? [message #427007 is a reply to message #427005] Tue, 20 October 2009 10:00 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
I check it ... it is running, and it give result, there is value in
detail block.
Re: duplicate record is not working ?? [message #427011 is a reply to message #426962] Tue, 20 October 2009 10:13 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to make your replies more detailed. Remember I can't see what you're doing.

What is running - the select in the form or the select in sqlplus?

Copy and paste and execution of your query from sqlplus along with the result.

Then can you please tell us what datablocks you've got in your form, what the relationship is between them and what database tables they correspond to.
Re: duplicate record is not working ?? [message #427013 is a reply to message #427011] Tue, 20 October 2009 10:20 Go to previous messageGo to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
What is running - the select in the form or the select in sqlplus? ----select in sqlplus.

LECT distinct 1
FROM PYPSTSD
WHERE PYPSTSD_PARTICIPANT_PIDM = 2500
and PYPSTSD_DATE = '11-OCT-2009'
and PYPSTSD_TRAINING_LOCN = 'LAN'
and PYPSTSD_TRAINING_CODE = 'RES'

result
==========
1


i have 2 data blocks in my form: PYPSTSM & PYPSTSD and the relation between them is:

PYPSTSD.PYPSTSD_DATE = PYPSTSM.PYPSTSM_DATE AND
PYPSTSD.PYPSTSD_TRAINING_LOCN = PYPSTSM.PYPSTSM_TRAINING_LOCN AND
PYPSTSD.PYPSTSD_TRAINING_CODE = PYPSTSM.PYPSTSM_TRAINING_CODE
Re: duplicate record is not working ?? [message #427014 is a reply to message #426962] Tue, 20 October 2009 10:31 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) I assume PYPSTSM is the master (parent) table and PYPSTSD the detail (child) table?

2) And the PYPSTSD_PARTICIPANT_PIDM column only exists on PYPSTSD?

3) So which datablock do the items in this select come from?
SELECT distinct 1 into :global.dummy
FROM PYPSTSD
WHERE PYPSTSD_PARTICIPANT_PIDM = :PIDM
and PYPSTSD_DATE = :PYPSTSD_DATE
and PYPSTSD_TRAINING_LOCN = :PYPSTSD_TRAINING_LOCN
and PYPSTSD_TRAINING_CODE = :PYPSTSD_TRAINING_CODE;

You really should use :<datablock>.<item_name> notation and not just :<item_name>, it makes the code a lot easier to follow.

4) How are the datablock items from 3 above populated?

5) What's the datatype of PYPSTSD_DATE?



And can you please use code tags for all code, including selects from sqlplus.
Previous Topic: raise form_trigger_failure in WHEN-VALIDATE-RECORD still saves data
Next Topic: problem Webutil WEBUTIL_FILE.FILE_SELECTION_DIALOG_INT will not work
Goto Forum:
  


Current Time: Wed Sep 28 21:11:22 CDT 2016

Total time taken to generate the page: 0.09457 seconds