Poorman’s MySQL table audit information – part 2

Mysql

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 !