title: MySQL trigger at specified time to create new entries into a table
body: <p>My problem is this, I have a list of students stored and each student attends school on certain days of the week. I want to create a trigger that runs at midnight that check what day it just became and if a student attends that day, will make a new entry into the database for each student. I have one partially created with a while loop, but I know it isn't the best solution. One big issue with this as it is now is that it would draw all of the StudentId's, not the single ID from the row it is currently examining. There are three tables that this will access Students, Schedule and ClassSession. Students and Schedule are already generated and multiple rows in ClassSession containing a StudentId and CurrentDate would be the outcome.</p>
  
  <pre><code>CREATE EVENT NewDailyTables
  ON SCHEDULE
      EVERY 1 DAY
      STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY)
    DO
      CALL CreateDayTriggerWhile();
  
  ------------------------------------------------------------------------------------------------------
  
  DELIMITER $$
  CREATE PROCEDURE `cnp_data`.`CreateDayTriggerWhile` ()
  BEGIN
      SET @ThisEnd=0;
      SET @Count=0;
      SET @ThisId=0;
      SELECT COUNT(*) FROM Schedule INTO @ThisEnd;
      WHILE @Count&lt;@ThisEnd DO
          SELECT StudentId FROM Schedule INTO @ ThisId; /* takes all student id's. Unsure of how to write WHERE row=@count*/
          CALL CreateDayTriggerHelp(@ThisId);
      END WHILE;
  END $$
  
  ------------------------------------------------------------------------------------------------------
  
  DELIMITER $$
  CREATE PROCEDURE `cnp_data`.`CreateDayTriggerHelp` (IN ThisId INT)
  BEGIN
  
      SET @DayName = DAYNAME(CURRENT_DATE);
      IF (SELECT ThisId FROM Schedule WHERE @DayName=1)=true
          THEN
              INSERT INTO ClassSession (StudentId, CurrentDate)
                  VALUES (ThisId, CURRENT_DATE);
          END IF;
  END $$
  
  -------------------------------------------------------------------------------------------------------
  
  </code></pre>
  
  <p>Thanks for your insight!</p>
  
  <p>Edit--</p>
  
  <p>This is how schedule looks</p>
  
  <pre><code>StudentId-Monday-Tuesday-Wednesday-Thursday-Friday-Saturday-Sunday
     2374  -   0  -   1   -    0    -    1   -  0   -    0   -  0
  </code></pre>
  
  <p>It store a tinyint/BOOL on the days the students show up to class. How I want to have it working is that it checks the day and then sees if the student is supposed to show up that day to class and creates the new entry into ClassSchedule if so</p>