MariaDB 10.4 INSERT触发器NEW列在WITH子查询中无法识别问题

这是MariaDB 10.4及更早版本的固有作用域限制：触发器的NEW伪行仅能在触发器顶层语句、非CTE结构的直接子查询中访问，嵌套WITH（公共表表达式）子查询属于独立的查询上下文，无法向上访问触发器层级的NEW伪行字段，因此会报字段不存在的错误。

方案1：通过用户变量传递值（推荐，写法简单） #

行级触发器每次触发时会独立执行上下文，直接将NEW的字段值提前存入会话临时变量，再在CTE中引用该变量即可，修改后的触发器代码如下：

DELIMITER //
CREATE DEFINER=`root`@`localhost` 
TRIGGER `calculMedie` 
AFTER INSERT ON `note` 
FOR EACH ROW
BEGIN
    -- 先把NEW的值存入临时变量
    SET @curr_nr_legitimatie = NEW.nr_legitimatie;
    INSERT INTO medii (MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3)
    WITH date AS (
        WITH medii_pe_coloane AS (
            SELECT medie1.nr_legitimatie, 
                AVG(medie1.maxim) as medie_an_1, 
                AVG(medie2.maxim) as medie_an_2, 
                AVG(medie3.maxim) as medie_an_3
            FROM 
            (
            SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
            FROM note 
            WHERE an_studiu = 1 AND nr_legitimatie = @curr_nr_legitimatie
            GROUP BY nr_legitimatie, disciplina
            ) as medie1,
            (
            SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
            FROM note 
            WHERE an_studiu = 2 AND nr_legitimatie = @curr_nr_legitimatie
            GROUP BY nr_legitimatie, disciplina
            ) as medie2,
            (
            SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
            FROM note 
            WHERE an_studiu = 3 AND nr_legitimatie = @curr_nr_legitimatie
            GROUP BY nr_legitimatie, disciplina
            ) as medie3
        ),
        medii_union AS (
            SELECT medii_pe_coloane.medie_an_1 as medie from medii_pe_coloane
            UNION ALL
            SELECT medii_pe_coloane.medie_an_2 as medie from medii_pe_coloane
            UNION ALL
            SELECT medii_pe_coloane.medie_an_3 as medie from medii_pe_coloane
        ),
        medie_generala AS (
            SELECT AVG(medii_union.medie) as medie FROM medii_union
        )
        SELECT medii_pe_coloane.*, medie_generala.medie from medii_pe_coloane, medie_generala
    )
    SELECT nr_legitimatie, medie, medie_an_1, medie_an_2, medie_an_3
    FROM date;
END //
DELIMITER ;

注：代码开头和结尾的DELIMITER语句用于临时修改SQL语句分隔符，避免触发器内的分号被提前识别为语句结束标记。

方案2：通过顶层CTE传递值（无变量） #

如果不想用用户变量，可以把NEW的值作为常量写在最外层CTE中，后续子查询通过关联获取值：

CREATE DEFINER=`root`@`localhost` 
TRIGGER `calculMedie` 
AFTER INSERT ON `note` 
FOR EACH ROW 
INSERT INTO medii (MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3)
WITH params AS (
    -- 顶层CTE可以访问NEW，先把值存到这里
    SELECT NEW.nr_legitimatie AS curr_nr
),
date AS (
    WITH medii_pe_coloane AS (
        SELECT medie1.nr_legitimatie, 
            AVG(medie1.maxim) as medie_an_1, 
            AVG(medie2.maxim) as medie_an_2, 
            AVG(medie3.maxim) as medie_an_3
        FROM 
        params,
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 1 AND nr_legitimatie = params.curr_nr
        GROUP BY nr_legitimatie, disciplina
        ) as medie1,
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 2 AND nr_legitimatie = params.curr_nr
        GROUP BY nr_legitimatie, disciplina
        ) as medie2,
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 3 AND nr_legitimatie = params.curr_nr
        GROUP BY nr_legitimatie, disciplina
        ) as medie3
    ),
    medii_union AS (
        SELECT medii_pe_coloane.medie_an_1 as medie from medii_pe_coloane
        UNION ALL
        SELECT medii_pe_coloane.medie_an_2 as medie from medii_pe_coloane
        UNION ALL
        SELECT medii_pe_coloane.medie_an_3 as medie from medii_pe_coloane
    ),
    medie_generala AS (
        SELECT AVG(medii_union.medie) as medie FROM medii_union
    )
    SELECT medii_pe_coloane.*, medie_generala.medie from medii_pe_coloane, medie_generala
)
SELECT nr_legitimatie, medie, medie_an_1, medie_an_2, medie_an_3
FROM date

内容的提问来源于stack exchange，提问作者Cristian Benescu