Some time ago I developed a utility Python app which does certain actions if it finds that any data in a database has been updated recently. To do this, for each database I created a table last_modif_info, with a single field dbase_last_modif.
Then I made the following trigger:
CREATE TRIGGER update_dbase_last_modif AFTER UPDATE
ON information_schema.tables
FOR EACH ROW
UPDATE last_modif_info SET last_modif_info.Dbase_last_modif = CURRENT_TIMESTAMP;
… this works as intended and has been doing so for months.
But now, as part of this utility app, I want somehow to detect that this trigger mechanism has been correctly set up. I’m wondering for example what happens if you create a new table after having created this trigger: presumably you have to recreate the trigger? If so, I’d want to find out which tables do or don’t have the trigger set up.
I did a dump of information_schema, structure only but including data for table “tables”.
In this dump neither the string “update_dbase_last_modif” nor the string “current_timestamp” appears, so it does not appear to contain instructions for setting up this trigger.
I recall from the time I was finding out how to implement this mechanism that some of the MariaDB/MySQL system databases are a bit more mysterious than normal databases.
Does anyone know how I can check on this trigger mechanism from analysing a dump or in some other way?
