MySQL触发器和SUM() [英] MySQL triggers and SUM()

问题描述

我有两个桌子，学生和家人.在学生中，我有st_income和total_income列.在家庭我有收入. Total_income是学生的st_income和家庭收入的总和，其中family.id_student = student.id_student.

I have two tables student and family. In student I have column st_income and total_income. In family I have income. Total_income is sum of st_income from student and sum of income from family where family.id_student=student.id_student.

我想通过触发器更新total_income，我做到了

I want to update total_income by trigger and I made this

CREATE TRIGGER family_income_update AFTER UPDATE ON `family` 
FOR EACH ROW UPDATE student SET total_income= ((SELECT SUM(income) 
FROM family WHERE family.id_student=student.id_student)+
(SELECT st_income FROM student WHERE student.id_student=NEW.id_student)) 
WHERE student.id_student=NEW.id_student 

MySQL接受了此触发器，但是当我想更新表族中的归档收入时，我得到了此信息:

MySQL accepted this trigger, but when I want to update filed income in table family I get this communicate:

我不知道如何解决这个问题.

I don't know how resolve this.

更新

我尝试过:

CREATE TRIGGER family_income_update AFTER UPDATE ON `family` 
FOR EACH ROW 

SET @familyIncome = SELECT SUM(income) FROM family WHERE family.id_student=student.id_student
SET @studentIncome= SELECT st_income FROM student WHERE student.id_student=NEW.id_student
SET @totalIncome=@familyIncome+@studentIncome

UPDATE student SET total_income=@totalIncome WHERE student.id_student=NEW.id_student

但是我得到了这个答案:

but I got this answers:

更新

我尝试将st_income的total_income值相加.这两列都在同一张表中.我试过了:

I try add to total_income value of st_income. Both column are in the same table. I tried this:

CREATE TRIGGER `st_income_update` AFTER UPDATE ON `student`
 FOR EACH ROW BEGIN
  UPDATE student
    SET total_income = total_income + (NEW.st_income - OLD.st_income)
    WHERE student.id_student = NEW.id_student;
END

但这会导致错误.

推荐答案

这就是所谓的更改表"问题.归结为，不允许行触发器访问同一表中的其他行，因为这不能保证行将按特定顺序进行更新.

This is what's known as the "Mutating Table" problem. What it boils down to is that a row trigger is not allowed to access other rows in the same table because there's no guarantee that rows will be updated in a specific order, among other things.

首先，当您可以在需要时轻松计算出total_income时，您实际上不应该尝试存储它.就是说，我认为您可以通过做类似的事情来完成自己想要的事情

First off, you really shouldn't be trying to store total_income when you can calculate it easily when needed. That said, I think you can do what you want by doing something like

CREATE TRIGGER family_income_update
  AFTER UPDATE ON family
  FOR EACH ROW 
BEGIN
  UPDATE student
    SET total_income = total_income + (NEW.income - OLD.income)
    WHERE student.id_student = NEW.id_student;
END;

想法是相对于student.total_income先前的值进行修改，因为您只能引用family.income相对于其先前的值.

The idea is to modify student.total_income relative to its previous value, since you can only reference family.income relative to its previous value.

这篇关于MySQL触发器和SUM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！