Cannot use CTE in trigger definition

create or replace table base_data(

    id int auto_increment not null primary key,

    record_data longtext not null check (json_valid(record_data))

);

insert into base_data(record_data)

values

('{"x":1, "y":[2,3,4]}')

;

create or replace table mv_integers as

with recursive array_values as (

    select 

        json_extract(b.record_data, '$.y[*]') val

        , b.id

        , json_length(json_extract(b.record_data, '$.y[*]')) n

        from base_data b

), rec_elems as (

    select av.id, 0 as i, json_extract(av.val, '$[0]') elem

    from array_values av

    where av.n > 0

    union all

    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))

    from array_values av

        inner join rec_elems e on av.id = e.id

    where (i + 1) < av.n

)

select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'

from rec_elems r

union all

select b.id, json_value(b.record_data, '$.x'), 'x'

from base_data b

;

create or replace trigger maintain_mv_integers

after insert on base_data for each row 

insert mv_integers(record_id, extracted_integer, comes_from)

with recursive array_values as (

    select 

        json_extract(new.record_data, '$.y[*]') val

        , new.id

        , json_length(json_extract(new.record_data, '$.y[*]')) n

), rec_elems as (

    select av.id, 0 as i, json_extract(av.val, '$[0]') elem

    from array_values av

    where av.n > 0

    union all

    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))

    from array_values av

        inner join rec_elems e on av.id = e.id

    where (i + 1) < av.n

)

select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'

from rec_elems r

union all

select new.id, json_value(new.record_data, '$.x'), 'x'

;

insert into base_data(record_data)

values

('{"x":5, "y":[6,7,4]}')

;