mysql - MariaDB / MySQL：在触发器中插入选择不会插入任何内容

Question

我正在尝试创建一个插入后触发器，vitamin以将新数据插入到vitamin_variety我创建的交叉连接选择语句中。但不知何故，当插入新行时，触发器不会将该新行插入vitamin_variety. 我将 select 语句与 join 一起使用，它本身就可以很好地工作，但是当我使用它插入 select 语句时，它什么也不做。

我的代码

BEGIN
INSERT INTO 
vitamin_variety (extreme_name,regular_name, price_variety_extreme,price_variety_regular) 

SELECT sel_extreme.name as `extreme_name`,sel_regular.name as `regular_name`,sel_extreme.ext,sel_regular.reg FROM

(SELECT sel.name as `name`,sel.id as `id`,sel.extreme as `ext` FROM
(SELECT new.vitamin_name as `name`,new.vitamin_id as `id`,CASE WHEN new.vitamin_name LIKE 'extreme%' THEN new.price END AS `extreme`
FROM vitamin) AS sel
WHERE sel.extreme IS NOT NULL) AS sel_extreme

CROSS JOIN

(SELECT sel.name as `name`,sel.id as `id`, sel.regular as `reg` FROM
(SELECT new.vitamin_name as `name`,new.vitamin_id as `id`,CASE WHEN new.vitamin_name NOT LIKE 'extreme%' THEN new.price END AS `regular`
FROM vitamin) AS sel
WHERE sel.regular IS NOT NULL) AS sel_regular

WHERE sel_regular.id <= sel_extreme.id
END

已编辑 这是FIDDLE

标签： mysqlmariadb