can't create a trigger in mysql

Question

i'm trying to create a trigger but there is an error somewhere and since i'm new at this i can't solve it...

So basically i have two tables:

students(stud_num:INT, grade_avg :real)

grading(stud_num:INT, classe:char(5), grade:int)

in students stud_num is the primary key, and in grading it references the table students..

What my professor wants is for me to create a trigger that every time we insert a grade in grading, the grade_avg is updated in students.

This is what i have so far:

DELIMITER %%

CREATE TRIGGER something

AFTER INSERT ON grading

FOR EACH ROW BEGIN

@stud_num=new.stud_num;

UPDATE students

SET grade_avg=(SELECT AVG(grade) FROM grading WHERE stud_num=@stud_num);

END;

%%

Can someone please help me?

according to mysql its a syntax error in '@stud_num=new.stud_num;' — user3038648
– user3038648, Commented Nov 28, 2013 at 14:30

Ike Walker · Accepted Answer · 2013-11-27 00:31:37Z

2

The problem is the way you use the user-defined variable, and you don't actually need it, so you can just skip that by using new.stud_num in place of @stud_num.

You should also restrict your update statement to only update the row for the relevant student, rather than all rows.

DELIMITER %%

CREATE TRIGGER something

AFTER INSERT ON grading

FOR EACH ROW BEGIN

UPDATE students
SET grade_avg=(
  SELECT AVG(grade) 
  FROM grading 
  WHERE stud_num=new.stud_num
)
WHERE stud_num=new.stud_num;

END;

%%

DELIMITER ;

answered Nov 27, 2013 at 0:31

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

can't create a trigger in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related