CREATE TRIGGER `after_customer_insert`
AFTER INSERT ON `customer`
FOR EACH ROW BEGIN
    UPDATE `user`
    SET
        `customers_count` = `customers_count` + 1
    WHERE `id` = NEW.`user_id`;
END$$

与其为客户中的每个新行调用UPDATE用户,不如将触发器作为“整体”？我的意思是

CREATE TRIGGER `after_customer_insert`
AFTER INSERT ON `customer`
BEGIN
    UPDATE `user`
    SET
        `customers_count` = (
            SELECT COUNT(`id`)
            FROM `customer`
            WHERE `user_id` = `id`
        )
END$$

解决方法:

正如杰克指出的那样,触发器主要用于执行与单行更改有关的动作,通常是更改另一行.当然,您可以实现对重复的每一行在很大程度上相同的结果集上(冗余)执行相同操作的逻辑,但是问题是,为什么不在原始UPDATE之后不发出单个UPDATE语句？您可能有充分的理由,因此,杰克(Jake)对总体情况有疑问.

例如(语法未经测试,但总体思路成立)

UPDATE u set u.customer_count = x.newcount
from
user u inner join 
(
select user, count(*) as newcount
from user group by customer_id
) x