如何从触发器MySQL更新多个列

hi i am using mysql trigger to update a table on another table's insertion

嗨,我使用mysql触发器更新另一个表的插入表

this trigger works fine

这个触发器工作正常

CREATE TRIGGER `update_pupil_subject` AFTER INSERT ON `pupil_marks`
FOR EACH ROW  
BEGIN
   UPDATE  pupil_subjects  SET NumberOfStudens = NumberOfStudens + 1  WHERE NEW.SubjectID = SubjectID;
END$$

but this gives an error

但这会给出错误

CREATE TRIGGER `update_pupil_subject` AFTER INSERT ON `pupil_marks`
FOR EACH ROW  
BEGIN
   UPDATE  pupil_subjects  SET NumberOfStudens = NumberOfStudens + 1 , AverageMarks = (SELECT AVG(Marks) FROM pupil_marks WHERE NEW.StudentID = StudentID ) WHERE NEW.SubjectID = SubjectID;
END$$

how to write this correctly , please help . thanks in advance .

如何正确写这个,请帮忙。提前致谢 。

2 个解决方案

#1

Apparently there were problems when sub-queries were used:

使用子查询时显然存在问题:

Can you try splitting the SQL statement:

您可以尝试拆分SQL语句:

DELIMITER $$

CREATE TRIGGER `update_pupil_subject`
   AFTER INSERT
   ON `pupil_marks`
   FOR EACH ROW
BEGIN
   DECLARE avg_marks   float;

   SELECT AVG(Marks)
     INTO avg_marks
     FROM pupil_marks
    WHERE NEW.SubjectID = SubjectID;

   UPDATE pupil_subjects
      SET NumberOfStudens = NumberOfStudens + 1, AverageMarks = avg_marks
    WHERE NEW.SubjectID = SubjectID;
END
$$

Edit: Use

SHOW TRIGGERS WHERE `table` = 'pupil_marks';

to get all triggers defined on pupil_marks. You can't have multiple triggers on an event as all actions can be covered in single trigger.

获取pupil_marks上定义的所有触发器。您不能在事件上拥有多个触发器,因为所有操作都可以在单个触发器中涵盖。

NOTE: I think AVG(Marks) is for a given subject, so modified trigger definition accordingly.

注意:我认为AVG(标记)是针对给定主题的,因此修改了触发器定义。

#2

declare a variable inside the trigger and assign it with the subquery

在触发器中声明一个变量并使用子查询分配它

declare avg_mark integer default 0; set avg_mark := (SELECT AVG(Marks) FROM pupil_marks WHERE NEW.StudentID = StudentID);

声明avg_mark整数默认为0; set avg_mark:=(SELECT AVG(Marks)FROM pupil_marks WHERE NEW.StudentID = StudentID);

then use the variable "avg_mark" in your update statement... it may work...

然后在更新语句中使用变量“avg_mark”...它可能有效......

if not then check the delimiter just below phpmyadmin sql box . It should be "$$"

如果没有,那么检查phpmyadmin sql框下方的分隔符。它应该是“$$”