mysql - MySQL触发器：未定义的变量'tableName'

Question

我看不出这个触发器定义有什么问题...我不知道选择查询是错误的还是其他地方。

这个想法是当在tableA中插入一行时，触发器SELECT在tableB上执行a以检索一些数据并插入或更新tableC。

错误：

SQL Error [1327] [42000]: Undeclared variable: aTable

触发 DDL：

DELIMITER //
CREATE TRIGGER my_trigger
AFTER INSERT
ON myTable
FOR EACH ROW
BEGIN
DECLARE varA integer;
DECLARE varB integer;

IF NEW.`Type` = 'comment' THEN
        
    SELECT
        myOtherTable.Id INTO varA
        ,aTable.Id INTO varB
    FROM myOtherTable <----- maybe here
    JOIN aTable ON aTable.id = myOtherTable.aTableId
    WHERE myOtherTable.Id = NEW.Id;

    Insert INTO myThirdTable VALUES 
        (NEW.Id, NEW.time, varA)
    ON DUPLICATE KEY UPDATE ...;
END IF;
END//
DELIMITER ;

更新 03/12/2020 似乎添加第二个INTO varB会导致aTable变量未声明的错误。

标签： mysqltriggersddl

Answer 1

测试单语句形式：

CREATE TRIGGER my_trigger
AFTER INSERT
ON myTable
FOR EACH ROW
INSERT INTO myThirdTable (id, `time`, a)
SELECT NEW.Id, NEW.time, myOtherTable.Id
FROM myOtherTable
WHERE myOtherTable.Id = NEW.Id
ON DUPLICATE KEY UPDATE ...;