0

Im just learning how to do triggers in mysql with php I have two versions of the trigger i would like to use but i keep getting errors saying the syntax is wrong.

the first one is:

CREATE TRIGGER credits_earned AFTER UPDATE ON takes on (grade)
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow 
FOR EACH ROW 
WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL 
  AND (orow.grade = 'F' OR orow.grade IS NULL)
BEGIN ATOMIC 
  UPDATE student 
  SET tot_credit = tot_cred +
 (SELECT credits
  FROM course
  WHERE course.course_id = nrow.course_id)
WHERE student.id = nrow.id;

and the second one is:

DELIMITER $$

CREATE TRIGGER grade_change AFTER UPDATE on takes
FOR EACH ROW BEGIN
IF (OLD.grade='F' OR OLD.grade IS NULL) AND NEW.grade != 'F' AND NEW.grade IS NOT NULL THEN
  BEGIN
    set @c=(SELECT credits FROM course WHERE course.course_id=NEW.course_id);
    UPDATE student SET tot_cred=tot_cred+@c WHERE student.id=NEW.id;
  END;
END IF;
END$$
DELIMITER ;

I've tried entering them in phpMyAdmin and in a php script. The update statement i'm using is

$update = "update takes
          set grade = 'B'
          where grade = 'F' or null";

could someone help me with the correct way to implement the trigger.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by urtrivedi
0

open phpmyadmin->database->sql

paste only TRIGGER CODE NOT DELIMITER LINES in sql textarea

CREATE TRIGGER grade_change AFTER UPDATE on takesFOR EACH ROW BEGINIF (OLD.grade='F' OR OLD.grade IS NULL) AND NEW.grade != 'F' AND NEW.grade IS NOT NULL THEN  BEGIN    SET @c=(SELECT credits FROM course WHERE course.course_id=NEW.course_id);    UPDATE student SET tot_cred=tot_cred+@c WHERE student.id=NEW.id;  END;END IF;END$$

at the bottom of sql textarea you will find delmiter field, there repalce ; with $$

then execute.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.