mysql - 'discussion.rubric_id' 中的未知列 'where clause'

Question

我需要重命名一些 MySQL 表，从而搞乱了我的程序。至少我是这么认为的......我收到了一个以前不存在的错误。

有两个表很重要:一个称为rubrics，它有一列用于统计与rubric 相关的讨论。因此，每当插入新的讨论时，就会触发一个过程，从而增加计数器。

这是两个表(简而言之):

这是我的标题表格

CREATE  TABLE IF NOT EXISTS `rubric` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `topic_id` INT NOT NULL ,
    `name` VARCHAR(50) NULL ,
    `description` TEXT NULL ,
    `total_discussions` INT NULL DEFAULT 0 ,  // this is the COUNTER
    `latest_activity_id` INT NULL ,
    PRIMARY KEY (`id`, `topic_id`) ,
... // just some index and constraint..
ENGINE = InnoDB;

这是我的讨论表

CREATE  TABLE IF NOT EXISTS `discussion` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `rubric_id` INT NOT NULL ,       // as you can see it has a rubric_id column
    `topic_id` INT NOT NULL ,
    `latest_activity_id` INT NULL DEFAULT 0 ,
    `name` VARCHAR(50) NULL ,
    `description` TEXT NULL ,
    `total_posts` INT NULL DEFAULT 0 ,
    `total_views` INT NULL DEFAULT 0 ,
    PRIMARY KEY (`id`, `rubric_id`, `topic_id`) ,
    ... // just some index and constraint..
ENGINE = InnoDB;

这是应该增加rubrics表中计数器的过程:

DELIMITER $$

CREATE TRIGGER count_discussions AFTER INSERT ON discussion
FOR EACH ROW
BEGIN
    UPDATE rubric SET total_discussions = total_discussions + 1 WHERE rubric.id = discussion.rubric_id;
END $$

插入新行时遇到的错误是:

Error code 1054, SQL state 42S22: Unknown column 'discussion.rubric_id' in 'where clause'

所以我猜测它与过程的WHERE子句有关，因为我没有其他WHERE子句提到< em>rubric_id 列。但正如您在讨论表中看到的那样，该专栏是存在的。这是拼写错误还是我不尊重的语法？

Answer 1

尝试一下:

   DELIMITER $$

   CREATE TRIGGER count_discussions AFTER INSERT ON discussion
   FOR EACH ROW
   BEGIN
   UPDATE rubric 
   INNER JOIN discussion ON rubric.id =   discussion.rubric_id 
   SET total_discussions = total_discussions + 1 ;
   END $$

您需要加入桌面讨论。