title: Alternative to using Prepared Statement in Trigger with MySQL
body: <p>I'm trying to create a MySQL Before Insert trigger with the following code which would do what I want it to do if I could find a way to execute the prepared statement generated by the trigger.</p>
  
  <p>Are the any alternative ways to execute prepared statements from within triggers? Thanks</p>
  
  <pre><code>BEGIN
      SET @CrntRcrd = (SELECT AUTO_INCREMENT FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA=DATABASE()
                AND TABLE_NAME='core_Test');
  
    SET @PrevRcrd = @CrntRcrd-1;
  
      IF (NEW.ID IS NULL) THEN
          SET NEW.ID = @CrntRcrd;
      END IF;
  
      SET @PrevHash = (SELECT Hash FROM core_Test WHERE Record=@PrevRcrd);
  
      SET @ClmNms = (SELECT CONCAT('NEW.',GROUP_CONCAT(column_name 
                    ORDER BY ORDINAL_POSITION SEPARATOR ',NEW.'),'')
                    FROM information_schema.columns 
                    WHERE table_schema = DATABASE() 
                    AND table_name = 'core_Test');
  
    SET @Query = CONCAT("SET @Query2 = CONCAT_WS(',','",@PrevHash,"','", @CrntRcrd, "',", @ClmNms, ");");
  
    PREPARE stmt1 FROM @Query;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1; 
  
    SET NEW.Hash = @Query2;
  END
  </code></pre>
  
  <p><strong>UPDATE / CLARIFICATION</strong>: The data will be stored in a table as below.</p>
  
  <pre><code>+------------+-----+------+----------------+
  | Record (AI)| ID  | Data | HASH           |
  +------------+-----+------+----------------+
  | 1          | 1   | ASDF | =DHFBGKJSDFHBG | (Hash Col 1)
  | 2          | 2   | NULL | =UEGFRYJKSDFHB | (Hash Col 1 + Col 2)
  | 3          | 1   | VBNM | =VKJSZDFVHBFJH | (Hash Col 2 + Col 3)
  | 4          | 4   | TYUI | =KDJFGNJBHMNVB | (Hash Col 3 + Col 4)
  | 5          | 5   | ZXCV | =SDKVBCVJHBJHB | (Hash Col 4 + Col 5)
  +------------+-----+------+----------------+
  </code></pre>
  
  <p>On each insert command the table will generate a Hash value for that row by appeding the pervious row's Hash value to a CONCAT() of the entire new row, then re-hashing the entire string. This will create a running record of Hash values for auditing purposes / use in another part of the application.</p>
  
  <p>My constraints are that this has to be done before the INSERT as rows cannot be updated afterwards.</p>
  
  <p><strong>UPDATE</strong>: I'm currently using the following code until I can find a way to pass the column names to CONCAT dynamically:</p>
  
  <pre><code>BEGIN
  
    SET @Record = (
      SELECT AUTO_INCREMENT FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA=DATABASE()
      AND TABLE_NAME='core_Test'    #&lt;--- UPDATE TABLE_NAME HERE
    );
    SET @PrevRecrd = @Record-1;
  
      IF (new.ID IS NULL) THEN
      SET new.ID = @Record;
    END IF;
  
    SET @PrevHash = (
      SELECT Hash FROM core_Test    #&lt;--- UPDATE TABLE_NAME HERE
      WHERE Record=@PrevRecrd
    );
  
    SET new.Hash = SHA1(CONCAT_WS(',',@PrevHash, @Record,
      /* --- UPDATE TABLE COLUMN NAMES HERE (EXCLUDE "new.Record" AND "new.Hash") --- */
      new.ID, new.Name, new.Data
    ));
  
  END
  </code></pre>