The "NEW" table in a trigger sometimes contains column and sometimes it does not-CodePudding

I've been trying to create an INSERT trigger and in a simple trigger body I can access the "NEW" table just fine.

But in an example where the "NEW" table is used deeper into the body it no longer finds the column I need.

I can find the column "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)

I can no longer find the column "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

I can not wrap my head around what could possibly happen. It's not overwritten. Maybe it's outside it's scope? How can I possibly use it in the WHERE clause?

The error is:

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

To test it I insert a new row in note table which 100% contains the "nr_legitimatie" column.

MariaDB 10.4

CodePudding user response：

I'm on my phone so haven't tested this, but perhaps you can't refer to NEW three times in the same trigger?

If so, I'd rewrite your query, which looks to have logical errors in it anyway.

This is my suggested query...

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

CodePudding user response：

In your common table expressions (CTEs) you do not mention the pseudo-table NEW in the FROM clause, so it is not in scope.

You could rewrite them with an inner join like

SELECT note.nr_legitimatie, note.disciplina, note.an_studiu, MAX(note.nota) AS maxim 
    FROM note INNER JOIN new on note.nr_legitimatie = new.nr_legitimatie
    WHERE note.an_studiu = 1 
    GROUP BY note.nr_legitimatie, note.disciplina

Don't forget that you must qualify all the column names as they will be duplicated between note and new.

If you used subqueries (where what you have put as CTEs appears as part of the FROM clause of the final SELECT), then the pseudo-table NEW is indeed in scope - this is a correlated subquery.