wh is wrong in this code? n which trigger shld i use? [message #600336] |
Tue, 05 November 2013 00:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/eb1f7206ff9897a211779833922d7cdc?s=64&d=mm&r=g) |
sanjlaxmi
Messages: 24 Registered: October 2013
|
Junior Member |
|
|
i want to fetch the a_name from applicant table if it is dere and want to show in name field(textbox) else it will b null..
and in which trigger shall i put this code so it will work
(after executing it will show output if the value is dere...so which trigger wiil b useful...)
declare
ct_no number :=0;
begin
select count(*) into :ct_no
from applicant
where appl_nmbr =:staff.ref.no;
if :ct_no > 0 then
:name :=a_name
else :name := null;
end if;
end;
pls give m esolution for this..
thanx in advance
|
|
|
|
|
|
|
|
|
Re: wh is wrong in this code? n which trigger shld i use? [message #600393 is a reply to message #600390] |
Wed, 06 November 2013 00:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Suppose that this is your APPLICANT table's contents (as far as APPL_NMBR equals 1):
APPL_NMBR A_NAME
1 sanjlaxmi
1 Littlefoot
In a POST-QUERY trigger, you chose to select A_NAME from that table where APPL_NMBR equals value stored in :STAFF.REF_NO item. In this case, :STAFF.REF_NO = 1. So, which one of A_NAME values (sanjlaxmi? Littlefoot?) do you want to display on the screen?
You said you want to put NULL into the first record's NAME item. OK, that could be done (you'd have to check :SYSTEM.TRIGGER_RECORD value and decide what to do). But then you'll come to the second record and the same SELECT statement will fail, again, because there'll be another :STAFF.REF_NO = 1, and query would - again - return TOO-MANY-ROWS.
Therefore: either there's something wrong with the APPLICANT table (lack of primary/unique key/index which allowed two (or even more) same values in APPL_NMBR column), or you have to add another condition(s) into POST-QUERY's WHERE clause which would make sure that only one record is returned, or find some other way out of it.
|
|
|
|