我的问题是,我有有效的触发器。我有2张桌子:
- 节点
- 表B
触发器内的查询有效,所以我想我搞砸了触发器,我不知道我做错了什么(以前从未使用过MySQL,所以这对我来说是一个猜谜游戏(
每当我删除表B和节点表并插入到节点中时,只有当我再次插入到节点中时,新值才会计入表B。 然后一切正常。我的 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//
任何帮助不胜感激,提前感谢
通过在 tableb 中的 uid 上设置唯一键,并使用 INSERT INTO ...在重复键更新时,您可以确保一行存在于表 B 中以保存论坛计数。
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 ;