1

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?

mike rodent
  • 111
  • 1
  • 8

1 Answers1

0

You can dump the triggers to an external file.

See my old post Can mysqldump dump triggers and procedures?

You can also execute this to see the trigger names and their definitions

SELECT
    trigger_name
   ,event_manipulation
   ,created
   ,action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'mydb' \G
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536