www.webdeveloper.com
Results 1 to 5 of 5

Thread: MySQL Triggers

  1. #1
    Join Date
    Apr 2008
    Posts
    38

    MySQL Triggers

    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.
    PHP Code:
    $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.
    Code:
    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.

  2. #2
    Join Date
    Apr 2008
    Posts
    38
    This is what I'm working on.
    Code:
    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.

  3. #3
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  4. #4
    Join Date
    Apr 2008
    Posts
    38
    Quote Originally Posted by 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.
    Yeah. I just got to figure out how to update just the Session_ID's I need.

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    wouldn't a stored procedure do the job better?
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles