MYSQL触发器仅在运行至少两次后更新

Question

我的问题是，我有有效的触发器。我有 2 张 table :

• 节点
• 表b

触发器内的查询有效，所以我认为我弄乱了触发器，而且我不知道我做错了什么(之前从未使用过MySQL，所以对我来说这是一个猜谜游戏)

每当我删除tableb和node表，并插入到node中时，新值不会被计入tableb，只有当我再次插入到node中时。然后一切正常。我的MySQL触发器如下:

DELIMITER //
  CREATE TRIGGER INSERT_USERS_FORUM 
  AFTER INSERT ON node
  FOR EACH ROW BEGIN

update tableb
set forum_count =  (select count(*) from node n, forum_index fi
     where n.nid = fi.nid   and fi.tid = 18
     and n.uid = new.uid
     group by n.uid)
where UID = new.uid;
END//

感谢任何帮助，提前致谢

Answer 1

通过在tableb中的uid上设置唯一键，并使用INSERT INTO ... ON DUPLICATE KEY UPDATE，您可以确保一行存在于 tableb 中以保存论坛计数。

DELIMITER //

CREATE TRIGGER INSERT_USERS_FORUM 
AFTER INSERT ON node
FOR EACH ROW BEGIN
    INSERT INTO `tableb`
    (`forum_count`)
    VALUES
    ((select count(*) from `node` n, `forum_index` fi
         where n.nid = fi.nid   and fi.tid = 18
         and n.uid = new.uid
         group by n.uid))
    ON DUPLICATE KEY UPDATE `forum_count` =  (select count(*) from `node` n, `forum_index` fi
         where n.nid = fi.nid   and fi.tid = 18
         and n.uid = new.uid
         group by n.uid)
    where `UID` = new.`uid`;
END//

DELIMITER ;