Home » Developer & Programmer » Forms » help on a button trigger (10g)
help on a button trigger [message #548656] Fri, 23 March 2012 17:23 Go to next message
vinni
Messages: 34
Registered: March 2012
Location: london
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. Sad
many thanks
apologies if the code looks complicated


Re: help on a button trigger [message #548698 is a reply to message #548656] Sat, 24 March 2012 12:26 Go to previous messageGo to next message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Both triggers would never execute ELSE part of the IF statement, because - when SELECT finds nothing, Oracle will raise NO-DATA-FOUND exception and, as you didn't handle it, will propagate it to end user. Therefore, perhaps you should do it as follows:
declare 
 ...
begin
  select ... 
    into ...
    from ...
    where ...;

  go_block(...);
exception
  when no_data_found then
    message('Your message');
    raise form_trigger_failure;
end;
Re: help on a button trigger [message #548703 is a reply to message #548698] Sat, 24 March 2012 13:27 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #548704 is a reply to message #548703] Sat, 24 March 2012 15:32 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
firstly, thanks for the replies...

and that 6 digit code was just an example of specific data underneath the unit_code column. (123456 being the unit_code for a subject like beginner maths)
'persons' is the name of the table (sorry about the poor choice of name).

my approach on designing that second trigger was to design it in a similar way to the login trigger but i got muddled up with the select statement.

basically i want my trigger to do this:
1) the logged in user (a.k.a CURRENT person_id) pushes the link
2) then that trigger checks the grade of the necessary subject (unit_code)
3) for example if the unit_code (which is 123456) = pass, then proceed to the next block
4) or else if the unit_code (123456) = fail, then you cannot proceed as you don't have a pass

i hope ive explained it better
many thanks for the support Smile

Re: help on a button trigger [message #548705 is a reply to message #548704] Sat, 24 March 2012 16:19 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #548709 is a reply to message #548705] Sat, 24 March 2012 16:56 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
the unit 123456 can = 'pass' or 'fail' because its a foreign key from another table called units.
the relationship between 'persons table' and 'grades table' is one to many
the relationship between 'units table' and 'grades table' is also one to many
the primary key is the grade_code which is also a sequence

therefore the grades table is looked at like this:

the table requires one to many person_ids and one to many unit_codes
each row defines whether that person has passed or failed a unit_code
Re: help on a button trigger [message #548713 is a reply to message #548709] Sun, 25 March 2012 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
vinni wrote on Sat, 24 March 2012 21:56
the 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 #548716 is a reply to message #548713] Sun, 25 March 2012 04:55 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
Grades table:
CONSTRAINT fk_Persons FOREIGN KEY (person_id) REFERENCES Persons (person_id),
CONSTRAINT fk_Units FOREIGN KEY (unit_code) REFERENCES Units (unit_code));
Re: help on a button trigger [message #548717 is a reply to message #548716] Sun, 25 March 2012 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok - so what is stopping you from adding the correct joins to the above select?

That is really basic sql.
Re: help on a button trigger [message #548718 is a reply to message #548717] Sun, 25 March 2012 05:41 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
i just wanted to know the correct structure to build the button trigger for my problem...
which is... if the current user has a pass in this unit,
then he/she can proceed to the next block, otherwise an alert will be raised
Re: help on a button trigger [message #548719 is a reply to message #548718] Sun, 25 March 2012 06:15 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot showed you that
Re: help on a button trigger [message #548724 is a reply to message #548719] Sun, 25 March 2012 10:41 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
ok thanks
and another question about global variables...
suppose i wanted to set the person_id as a global variable on the trigger for the login form
what would be the correct structure to accomplish this?
Re: help on a button trigger [message #548740 is a reply to message #548724] Sun, 25 March 2012 12:51 Go to previous messageGo to next message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Global variables are very well described in Forms Help. Say if you need additional explanation.
Re: help on a button trigger [message #548746 is a reply to message #548740] Sun, 25 March 2012 13:46 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
i think i do need further explanation please, its a topic that wasnt really taught whilst on my studies.
i thought i'd make things easier by showing u my next attempt at the login trigger

declare
:global.user_id '8';
user_pword varchar(16);
alert_button number;

begin
	
	select person_id, person_pword
	into :global.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
                :global.user_id:=user_id;		
                Go_block('MENU');--SPECIFY A BLOCK
		
	else
		alert_button := show_alert('Incorrect details');
	end if;
exception
  when no_data_found then
    message('Meaningful alert message');
    raise form_trigger_failure;

	end;


Thanks
Re: help on a button trigger [message #548753 is a reply to message #548746] Mon, 26 March 2012 00:43 Go to previous message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
:global.user_id '8';

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.
Previous Topic: Dynamical Alert box
Next Topic: Two Languages in Form 6i
Goto Forum:
  


Current Time: Mon Sep 22 12:14:15 CDT 2014

Total time taken to generate the page: 0.05184 seconds