MySQL Trigger - summarize changes in json formatted column

Question

I am trying to create multiple triggers for MySQL tables to track changes. I thought of a table for example

CREATE TABLE IF NOT EXISTS `contacts_changes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `action` enum('insert','update','delete') NOT NULL, `contact_id` int(6) NOT NULL, `changes` text NOT NULL, PRIMARY KEY (`id`) ); 

where contact_id is the foreign key to the table I want to track, and in the column changes I want to save the changes made using the JSON format.

Therefore, if I change the last name and birthday of the entry in the contact table, the change column should contain {"familyname": "Smith", "birthday": "1982-06-24} .

I found many examples of using triggers to track changes, but not one of them summarizes the changes made in a single column on a single row. They do this to insert a new line for each change made, i.e. One line to change the birthday and the other to change the last name.

Due to my ignorance of the (My) SQL language, I could not figure out whether it is possible to do something that I had in mind, and if so, how it can be done.

Any ideas or tips on how to solve this?

Thanks!

Edit: Sent back as a response.

Answer 1

Well, I decided it myself. It was not so difficult ...;)

 DELIMITER ;; CREATE TRIGGER `contacts_bu` BEFORE UPDATE ON `contacts` FOR EACH ROW BEGIN SET @json = "{"; SET @first = true; IF (OLD.name!=NEW.name) THEN SET @first = false; SET @json = CONCAT(@json, "\"name\"", ":", "\"", NEW.name, "\""); END IF; IF (OLD.birthdate!=NEW.birthdate) THEN IF ( !@first ) THEN SET @json = CONCAT(@json, ","); END IF; SET @first = false; SET @json = CONCAT(@json, "\"birthdate\"", ":", "\"", NEW.birthdate, "\""); END IF; SET @json = CONCAT(@json, "}"); INSERT INTO contacts_changes (`action`, `contact_id`, `changes`) VALUES ('update', NEW.id, @json); END;; DELIMITER ;