mysql - 触发更新MySQL中同一张表中的多条记录

Question

我有一个简单的表格:

CREATE  TABLE `accounting`.`People` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  `Property_number` VARCHAR(45) NULL ,
  `People_at_Location` INT NULL ,
  PRIMARY KEY (`ID`) );

INSERT INTO `accounting`.`People` (`Name`, `Property_number`, `People_at_Location`) VALUES ('Jim', '13', '2');
INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Tony', '16');
INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Alice', '9');
INSERT INTO `accounting`.`People` (`Name`, `Property_number`, `People_at_Location`) VALUES ('Martha', '13', '2');
INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Vandy', '');

在我们的数据中，我们知道每一行/记录的名称。但是当我们开始时，我们没有 Property_number。当我们收到客户的回邮时，我们会得到他们的 Property_number，并更新记录。

我们真正需要的是一个触发器，它查看 Property_number 并查询数据库中有多少其他记录具有相同的属性编号，并更新所有记录，因为我们现在知道该 Property_number 有另一个人。

例如(给定上面的示例数据)它看起来像:

ID     Name     Property_number     People_at_location
1      Jim      13                  2
2      Tony     16                  Null
3      Alice    9                   1
4      Martha   13                  2
5      Vandy    Null                Null      

因此我们从 Vandy 那里获得了新信息，告诉我们她在 property_number 13 中。我们想要更新记录 1、4 和 5 以反射(reflect)更新后的 People_at_location 计数。

ID     Name     Property_number     People_at_location
1      Jim      13                  3
2      Tony     16                  Null
3      Alice    9                   1
4      Martha   13                  3
5      Vandy    13                  3      

这个触发器会是什么样子？

Answer 1

一般形式是这样的(根据内存完成，所以可能会有一些语法错误):

CREATE TRIGGER update_people_at_location
    AFTER UPDATE ON People FOR EACH ROW
    BEGIN 
        // make sure we updated the property number from a NULL value to a non null
        // depending on your exact use case you may or may not want that check
        IF (OLD.Property_number IS NULL AND NEW.Property_number IS NOT NULL) THEN
           -- store the count for this property_number
           -- we are in an AFTER UPDATE trigger so the update is already done,
           --   which means this count will include the newly set value
           DECLARE total_people_at_location int;
           SELECT COUNT(*) INTO total_people_at_location FROM People WHERE Property_number = NEW.Propery_number;
           -- update the rows with the proper count
           UPDATE People SET People_at_location = total_people_at_location WHERE Property_number = NEW.Propery_number;
        END IF;
    END 

这也适用于当前计数为 NULL 的记录(例如您示例中的 ID 2)，尽管这些记录在您当前的数据状态下显然是错误的(我看不出任何原因为什么你有一个非 NULL Property_number 而是一个 NULL People_at_location，这没有意义)；

我想您可能想对新记录的插入执行相同的计算，在这种情况下，您应该将逻辑提取到存储过程中，并在触发器期间调用该过程，而不是重复代码。