从 MySQL 触发器动态更新交叉表视图

Question

我正在尝试对 MySQL 服务器上动态生成的交叉表视图中的某些数据进行反规范化。由于这里有一个有用的答案，我已经成功创建了一个生成此交叉表视图的存储过程。

这是我的过程：

DELIMITER $$

CREATE PROCEDURE `PartFlow`.`DimMatTab` ()
BEGIN
    SET @cols = (
    SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            '\nSUM(IF(dimension_id = ',
            dimension_id,
            ', value, NULL)) AS ',
            name
        )
    ) as column_list
    FROM PartFlow.tDimMatMap
    JOIN PartFlow.tDimension
        on tDimension.id_dimension = tDimMatMap.dimension_id
    );

    SET @sql = CONCAT(
        'CREATE OR REPLACE VIEW `PartFlow`.`vDimMatTab` AS '
        'SELECT material_id, ',
        @cols,
        ' FROM tDimMatMap ',
        'GROUP BY material_id;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END

生成一个包含此查询的视图：

select
`material_id`
,sum(if((`dimension_id` = 2),`value`,NULL)) AS `Width`
,sum(if((`dimension_id` = 3),`value`,NULL)) AS `Height`
,sum(if((`dimension_id` = 5),`value`,NULL)) AS `Thickness`
from `PartFlow`.`tDimMatMap`
group by `material_id`

我希望在对 tDimMatMap 进行任何更改时更新该视图，以便该视图始终包含正确的交叉表列。我已尝试在此源表上的任何插入、更新或删除操作上触发该过程。这是我的触发器代码：

create trigger update_view_iMat after insert on tDimMatMap
for each row call DimMatTab();$$

create trigger update_view_uMat after update on tDimMatMap
for each row call DimMatTab();$$

create trigger update_view_dMat after delete on tDimMatMap
for each row call DimMatTab();$$

在我看来，这是一个完美的设置，但是当我尝试编辑 tDimMatMap 表时，我收到错误消息“存储函数或触发器中不允许使用动态 SQL”。有什么办法可以解决这个问题吗？我是否从错误的角度处理问题？我只是想确保我的交叉表视图始终包含 tDimMatMap 表中列出的每个唯一“dimension_id”的列。

我从多个客户端前端访问这些数据，因此这种集中式解决方案似乎是正确的。我确信有些人会说我应该在服务器外部生成 SQL 代码，或者运行过程来获取数据而不创建视图，但我无法在客户端内动态更新此视图，也无法通过调用我的客户内部的程序。最重要的是，该视图将连接到某些应用程序中的其他视图和表，因此创建视图非常重要。