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.


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

drop trigger demo2_update$$
create trigger demo2_update before update on demo2
for each row
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(“old”, old.id,
“new”, new.id)
) into modif;
end if;
if old.firstname <> new.firstname then
select json_merge_patch(modif,
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(“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,
“$.modifications”, modif));
set new.audit_info=json_merge_patch(old.audit_info,
“$.modifications”, modif));
end if;


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 !