MySQL Triggers

[SFDonovan]

I am running MySQL 5.0.45 on a Mac OSX

I want to implement a trigger to update a value if another value is changed.

I have students grades for three types of exams for a class. I have the number of questions per exam written to a field. I take the raw score from a students exam and compute the grade for that test.

Example:
Class - PODS
Exam - Hyper IgM Deficiencies
Exam Type - GRAT
NumQuestions = 16
GRAT_Raw = 14
GRAT_Grade = %

SET GRAT_Grade = (($GRAT_Raw / $NumQuestions) * 100)

If for whatever reason someone would go back and increase or decrease the NumQuestions for this exam I want a trigger to compute GRAT_Grade.

Here is my table schema.


atlas_tl_session
`Session_ID` int(11) NOT NULL auto_increment,
`Course_ID` varchar(25) NOT NULL default '',
`Course_Number` mediumint(6) NOT NULL,
`Director_ID` varchar(25) NOT NULL default '',
`Session_Name` varchar(50) NOT NULL default '',
`Session_Detail` varchar(50) default NULL,
`num_quest_irat` tinyint(2) NOT NULL,
`num_quest_grat` tinyint(2) NOT NULL,
`num_quest_appex` tinyint(2) NOT NULL,

atlas_tl_session_grades
`Session_ID` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
`UID` varchar(9) default NULL,
`Group_ID` int(11) NOT NULL default '0',
`IRAT_Raw` decimal(3,1) NOT NULL,
`GRAT_Raw` decimal(3,1) NOT NULL,
`AppEx_Raw` decimal(3,1) NOT NULL,
`IRAT_Grade` decimal(5,2) default NULL,
`GRAT_Grade` decimal(5,2) default NULL,
`AppEx_Grade` decimal(5,2) default NULL,



Somebody could go back and change any of the three types of exams. IRAT, GRAT or AppEx.

Here is how I currently update the GRAT grade.

$Session_ID = $_POST[Session_ID];
$Group_ID = $_POST[Group_ID];
$GRAT_Raw = $_POST[GRAT_Raw];

$setrawscore = $db->sql_query("UPDATE ".$prefix."_tl_session_grades SET GRAT_Raw='$GRAT_Raw' WHERE Session_ID='$Session_ID' AND Group_ID = '$Group_ID'");

$getnumquestions = $db->sql_query("SELECT num_quest_grat FROM ".$prefix."_tl_session WHERE Session_ID = '$Session_ID'");
while($info = $db->sql_fetchrow($getnumquestions)) {
$NumQuestions = $info['num_quest_grat'];
}

$setgrade = $db->sql_query("UPDATE ".$prefix."_tl_session_grades SET GRAT_Grade = (($GRAT_Raw / $NumQuestions) * 100) WHERE Session_ID='$Session_ID' AND Group_ID = '$Group_ID'");
if (!$setgrade) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}

I just need to modify this into a trigger.

CREATE TRIGGER updategrades AFTER UPDATE ON atlas_tl_session_grades
FOR EACH ROW BEGIN
UPDATE atlas_tl_session_grades SET GRAT_Grade = etc

END;

If anybody has any experience with this it would be most appreciated.

[SFDonovan]

This is what I'm working on.

CREATE TRIGGER updategrades AFTER UPDATE ON atlas_tl_session_grades
FOR EACH ROW
BEGIN

SELECT DISTINCT num_quest_grat FROM atlas_tl_session a
JOIN atlas_tl_session_grades b WHERE a.Session_ID = b.Session_ID AND a.Calendar_Year = b.Academic_Year

UPDATE atlas_tl_session_grades SET GRAT_Grade = ((GRAT_Raw / num_quest_grat) * 100)
WHERE b.Session_ID = a.Session_ID


END;

Before I use $_POST to pass in my Session_ID values, so I don't know if this will work.

[chazzy]

triggers are called outside the context of any input. there's nothing you can pass into them. though, they should be able to access the row you updated.

[SFDonovan]

triggers are called outside the context of any input. there's nothing you can pass into them. though, they should be able to access the row you updated.

Yeah. I just got to figure out how to update just the Session_ID's I need.

[chazzy]

wouldn't a stored procedure do the job better?