Question 63950 in PhpMyAdmin Question posted in
The official documentation can be found here.

The "NEW" table in a trigger sometimes contains column and sometimes it does not – Phpmyadmin

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    This is a MariaDB bug (see especially this comment with a reproducible example close to your problem) with a misleading error message.

    Your bug occurs in a trigger when you reuse a CTE that uses new.

    In your case, you are using medii_pe_coloane (that uses new) 3 times in medii_union and once in the final select of date. This triggers the bug.

    You can use new itself as often as you want (in as many CTEs as you want) though. So a workaround would be to redefine the CTE several times,
    e.g. something like

    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

    Makes your code a bit repetitive, but should work as a general workaround. Alternatively, you may be able to rewrite and/or simplify your query otherwise (see e.g. MatBailies answer).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search