| duplicate record is not working ?? [message #426962] |
Tue, 20 October 2009 07:17  |
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  |
cookiemonster
Messages: 13963
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 #426984 is a reply to message #426980] |
Tue, 20 October 2009 08:31 |
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 |
cookiemonster
Messages: 13963
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 #426996 is a reply to message #426962] |
Tue, 20 October 2009 09:07 |
cookiemonster
Messages: 13963
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 |
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 #427011 is a reply to message #426962] |
Tue, 20 October 2009 10:13 |
cookiemonster
Messages: 13963
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 |
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 |
cookiemonster
Messages: 13963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
