help on a button trigger [message #548656] |
Fri, 23 March 2012 17:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4e0edb9cc196e382a185602b2427307b?s=64&d=mm&r=g) |
vinni
Messages: 34 Registered: March 2012
|
Member |
|
|
Hello im new to this
i'm designing a DB_application where a user makes certain subject selections depending on what grades they have. so far ive made simple signin form as follows....
Create table Persons(
person_id number(8) constraint pk_Persons primary key,
person_pword varchar2(16) not null;
declare
user_id number(8);
user_pword varchar(16);
alert_button number;
begin
select person_id, person_pword
into user_id, user_pword
from Persons
where :signin.txt_username = Persons.person_id;
if(user_pword =:login.txt_pword AND user_id =:signin.txt_username)
then
Go_block('MENU');--SPECIFY A BLOCK
else
alert_button := show_alert('Incorrect details');
end if;
end;
now the next step... that SIGNED IN user is taken to a menu with links to select their topics or units. HOWEVER only a PARTICULAR link can be followed depending on what grade they've achieved in a NECESSARY UNIT for example to do advanced maths u need beginners maths = PASS. if = fail then user cannot proceed.
heres my sql as well as the trigger ive made an attempt at...
Create table Grades(
grade_code number constraint pk_Grades primary key,
grade varchar(4) check (result in ('fail','pass')),
person_id number,
unit_code number,
CONSTRAINT fk_Persons FOREIGN KEY (person_id) REFERENCES Persons (person_id),
CONSTRAINT fk_Units FOREIGN KEY (unit_code) REFERENCES Units (unit_code));
declare
user_id number(8);
usergrade_code number;
usergrade varchar(4);
user_unitcode number(6);
alert_button number;
begin
select person_id, grade_code, grade, unit_code
into user_id, usergrade_code, usergrade, user_unitcode
from Grades
where Grades.unit_code.123456 = 'pass' AND = :signin.txt_username = Persons.person_id; /* 123456 being the unit code in question */
if Grades.unit_code.123456 = pass
THEN
Go_block('NEW_UNITS')
else
alert_button := show_alert('dont qualify for this unit');
end if;
end;
i know my second trigger is poorly designed and lacking. but please, it would be very helpful if you could guide me on the correct path.
many thanks
apologies if the code looks complicated
|
|
|
|
Re: help on a button trigger [message #548703 is a reply to message #548698] |
Sat, 24 March 2012 13:27 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
This select is meaningless:
select person_id, grade_code, grade, unit_code
into user_id, usergrade_code, usergrade, user_unitcode
from Grades
where Grades.unit_code.123456 = 'pass' AND = :signin.txt_username = Persons.person_id;
What is 123456?
What is persons?
|
|
|
|
Re: help on a button trigger [message #548705 is a reply to message #548704] |
Sat, 24 March 2012 16:19 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If the actual data is 123456 it can't ever = 'pass' or 'fail' can it?
So what is the actual relationship between unit_code and pass/fail?
And as for persons, there's nothing wrong with the name, but you can't just reference a table in the where like that without including it in the from.
What is the relationship between grades and persons?
|
|
|
|
Re: help on a button trigger [message #548713 is a reply to message #548709] |
Sun, 25 March 2012 04:01 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vinni wrote on Sat, 24 March 2012 21:56the unit 123456 can = 'pass' or 'fail' because its a foreign key from another table called units.
That would require a join to the units table in the query. There obviously isn't one at the moment.
vinni wrote on Sat, 24 March 2012 21:56
the relationship between 'persons table' and 'grades table' is one to many
Joining on which column?
vinni wrote on Sat, 24 March 2012 21:56
the relationship between 'units table' and 'grades table' is also one to many
Joining on which column?
Your query needs to join those three tables. It isn't. Add the missing joins.
|
|
|
|
|
|
|
|
|
|
Re: help on a button trigger [message #548753 is a reply to message #548746] |
Mon, 26 March 2012 00:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:
Eh? Please, do yourself a favor and READ what I told you to. In order to do that, while in Forms Builder, go to Help menu, choose Online Help, go to Search tab, search for "global variables" and read what's in there.
|
|
|