Poorman’s MySQL table audit information – part 2

I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare to yesterday, only the trigger on update is different:

DELIMITER $$
drop trigger demo2_update$$
create trigger demo2_update before update on demo2 
         for each row
         BEGIN
            declare modif json default null;            
            declare modif_arr json default null;           
            select json_object('updated_at', now(), 
                    'updated_by', session_user()) into modif;
            if old.id <> new.id then
               select json_merge_patch(modif, 
                      json_object('id', 
                        json_object("old", old.id, 
                                    "new", new.id)
                      )
               ) into modif;
            end if;
            if old.firstname <> new.firstname then
               select json_merge_patch(modif,
                      json_object('firstname', 
                        json_object("old", old.firstname, 
                                    "new", new.firstname)
                      )
               ) into modif;
            end if;
            if old.lastname <> new.lastname then
               select json_merge_patch(modif, 
                      json_object('lastname', 
                        json_object("old", old.lastname, 
                                    "new", new.lastname)
                      )
               ) into modif;
            end if;

            if json_extract(old.audit_info, "$.modifications") 
               is NULL then
              set new.audit_info=json_merge_patch(old.audit_info,
                  json_set(old.audit_info, 
                           "$.modifications", modif));              
            else                
              set new.audit_info=json_merge_patch(old.audit_info,
                  json_array_append(old.audit_info,
                           "$.modifications", modif));
            end if; 
END$$

DELIMITER ;

All the magic is included in this trigger above 😉

Now, it could be nice to have a store procedure or a script that could add the invisible JSON column and generate those triggers automatically for each columns for a table we want to keep audit information (and remove them too).

Enjoy MySQL !