stored procedures - Dynamically Update Crosstab View from MySQL Trigger -

i'm attempting de-normalize data in crosstab view dynamically generated on mysql server. due helpful answer here, i've created stored procedure generates crosstab view.

this procedure:

delimiter $$  create procedure `partflow`.`dimmattab` () begin     set @cols = (     select     group_concat(distinct         concat(             '\nsum(if(dimension_id = ',             dimension_id,             ', value, null)) ',             name         )     ) column_list     partflow.tdimmatmap     join partflow.tdimension         on tdimension.id_dimension = tdimmatmap.dimension_id     );      set @sql = concat(         'create or replace view `partflow`.`vdimmattab` '         'select material_id, ',         @cols,         ' tdimmatmap ',         'group material_id;');     prepare stmt @sql;     execute stmt; end 

which produces view containing query:

select `material_id` ,sum(if((`dimension_id` = 2),`value`,null)) `width` ,sum(if((`dimension_id` = 3),`value`,null)) `height` ,sum(if((`dimension_id` = 5),`value`,null)) `thickness` `partflow`.`tdimmatmap` group `material_id` 

i'd view updated on changes tdimmatmap, view contain right crosstab columns. i've tried triggering procedure on insert, update, or delete on source table. here trigger code:

create trigger update_view_imat after insert on tdimmatmap each row call dimmattab();$$  create trigger update_view_umat after update on tdimmatmap each row call dimmattab();$$  create trigger update_view_dmat after delete on tdimmatmap each row call dimmattab();$$ 

in mind, perfect setup, when try edit tdimmatmap table, error message, "dynamic sql not allowed in stored function or trigger". there way around this? approaching problem wrong angle? want make sure crosstab view contains column every unique "dimension_id" listed in tdimmatmap table.

i accessing data multiple client front-ends, centralized solution seems right. i'm sure should generate sql code outside server, or run procedure grab data without creating view, cannot dynamically update view within clients, nor can query calling procedure within clients. importantly, view joined other views , tables in applications, creating view pretty important.