title: Building JSON in MySQL trigger AFTER UPDATE
body: <p>It would be nice to get some help for implementing a trigger in MySQL 5.6 to log updated information.</p>
  
  <p>First of all, this is my data table and my log table:</p>
  
  <pre><code>CREATE TABLE t1
  (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
   data1 VARCHAR(255) NOT NULL,
   data2 DECIMAL(5,2) NOT NULL
  );
  
  CREATE TABLE t1_log
  (action VARCHAR(10) NOT NULL,
   timestamp TIMESTAMP NOT NULL,
   id INTEGER NOT NULL,
   data1 VARCHAR(255) NOT NULL,
   data2 DECIMAL(5,2) NOT NULL,
   data1_old VARCHAR(255),
   data2_old DECIMAL(5,2)
  );
  </code></pre>
  
  <p>And this is my trigger:</p>
  
  <pre><code>CREATE TRIGGER after_update_t1 AFTER UPDATE ON t1
   FOR EACH ROW
   BEGIN
     INSERT INTO t1_log (action,timestamp,id,data1,data2,data1_old,data2_old)
     VALUES ('update',NOW(),NEW.id,NEW.data1,NEW.data2,OLD.data1,OLD.data2);
   END;
  </code></pre>
  
  <p>That works without any problems. But this solution has the disadvantage that I need a log table for every data table.  
  So I thougt about a possibility to have only one log table with a BLOB field, combine the updated fields to an JSON string and store it in this BLOB. But how to define a trigger for that?</p>
  
  <p>In pseudocode it looks like this:</p>
  
  <pre><code>BEGIN
    SET jsonString = "{"
    FOR EACH field in NEW
       jsonString += field.name + ":" + field.value + ","
    END FOR
    FOR EACH field in OLD
       jsonString += field.name + ":" + field.value + ","
    END FOR
    jsonString = TRIM(jsonString, ",") + "}"
    INSERT INTO log (action,timestamp,jsondata)
    VALUES ('update',NOW(),jsonString)
  END
  </code></pre>