'where子句‘中未知列'discussion.rubric_id’

Question

我需要重命名几个MySQL表，从而搞乱了我的过程。至少我是这么想的。我发现了一个以前不存在的错误。

两个表很重要:一个是rubrics，它有一个列来计数与rubric相关的discussions。因此，每当插入新的discussion时，就会触发一个procedure来增加计数器。

以下是两个表格(简言之)：

这是我的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子句提到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 $$

你得参加桌面讨论。