触发器中的"NEW"表有时包含列，有时不包含列

Question

我一直在尝试创建一个INSERT触发器，在一个简单的触发器主体中，我可以访问；NEW"；桌子还可以。

但是在一个例子中；NEW"；表在正文中使用得更深，它不再能找到我需要的列。

我可以找到列"；NEW.nr_legitimatie"；in:

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)
VALUES (new.nr_legitimatie, 1, 2, 3, 4)

我再也找不到"；NEW.nr_legitimatie"；in:

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 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 = new.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 = new.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 = new.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

我无法理解可能发生的事情。它不会被覆盖。也许它超出了它的范围？如何在WHERE子句中使用它？

错误为：

#1054 - Unknown column 'nr_legitimatie' in 'NEW'

为了测试它，我在CCD_ 1表中插入一个新行；nr_ legitimatie"；柱

MariaDB 10.4

Answer 1

也许您不能在同一个触发器中引用NEW三次？

如果是这样的话，我会重写您的查询，不管怎样，它看起来都有逻辑错误。

这是我(未经测试(建议的查询：

INSERT INTO
medii (
MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3
)
WITH
max_an_studiu AS
(
SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
FROM note 
WHERE an_studiu IN (1, 2, 3)
AND nr_legitimatie = (SELECT nr_legitimatie FROM new)
GROUP BY nr_legitimatie, disciplina, an_studiu
),
medie AS
(
SELECT
nr_legitimatie,
an_studiu,
AVG(maxim) AS medie
FROM
max_an_studiu
GROUP BY
nr_legitimatie,
an_studiu
)
SELECT
nr_legitimatie,
AVG(medie)                                AS medie,
MAX(CASE WHEN an_studiu=1 THEN medie END) AS medie_an_1,
MAX(CASE WHEN an_studiu=2 THEN medie END) AS medie_an_2,
MAX(CASE WHEN an_studiu=3 THEN medie END) AS medie_an_3
FROM
medie
GROUP BY
nr_legitimatie

Answer 2

这是一个MariaDB错误(请特别参阅此注释，其中有一个接近您问题的可复制示例(，带有误导性错误消息。

当您重用使用new的CTE时，您的错误会出现在触发器中。

在您的情况下，您在medii_union中使用medii_pe_coloane(即使用new(3次，在date的最终选择中使用一次。这会触发错误。

不过，您可以随心所欲地使用new本身(在任意数量的CTE中(。因此，一种变通方法是多次重新定义CTE，例如类似的东西

WITH medii_pe_coloane AS ( <query using "new"> ),
medii_pe_coloane1 AS ( <same query using "new"> ),
medii_pe_coloane2 AS ( <same query using "new"> ),
medii_pe_coloane3 AS ( <same query using "new"> ),
...
medii_union AS (
select medii_pe_coloane1.medie_an_1 ... from medii_pe_coloane1 
union all select medii_pe_coloane2.medie_an_2 ... from medii_pe_coloane2
union all select medii_pe_coloane3.medie_an_3 ... from medii_pe_coloane3)
... 
SELECT ... from medii_pe_coloane, medie_generala

使您的代码有点重复，但应该作为一种通用的解决方法。或者，您也可以重写和/或简化查询(例如，请参阅MatBailies的答案(。