title: Creating views dynamically MySQL
body: <p>I have a table ..</p>
  
  <p><img src="https://i.stack.imgur.com/TKcke.png" alt="MySQL table"></p>
  
  <p>this table gets updated with new records everytime from the client users.</p>
  
  <p>What i want is to create a view for all distinct values of team_id, team_id values arent fixed .. can be in range 1-16 at 1 time and 1-8 at another time .. in any range .. </p>
  
  <p>So what i want is to create views dynamically( => trigger) on every record(View name as: view_of_team + NEW.team_id) insertion in this table ..</p>
  
  <p>I have tried executing create view statements in after insert triggers but MySQL wasnt just creating the trigger .. i then landed upon calling a procedure from after insert trigger</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `create_view_if_not_exists_stored_proc`(IN `team_id` INT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER 
  if not exists(select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_TYPE like 'VIEW' and TABLE_NAME like concat('view_of_team', team_id)) then
  
          SET @ViewName = concat('view_of_team', cast(team_id as char(50)));
          SET @sql = concat('create view ', @ViewName, ' as select * from mini_events_score_log where team_id=', team_id);
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
  end if
  </code></pre>
  
  <p>and using <code>CALL create_view_if_not_exists_stored_proc(NEW.team_id)</code> inside after insert trigger for the table shown above</p>
  
  <p>Executing procedure alone in my PhpMyAdmin creates views but the error arises: "Dynamic SQL not allowed in stored function or trigger" when trigger is executed after inserting a new record ..</p>
  
  <p>Is there a way to create views dynamically in this case ..</p>