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:
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 !