Search code examples
mysqltriggersdatabase-partitioning

MySQL: is a TRIGGER ON DELETE triggered by DROP PARTITION?

In my MySQL base, with engine=innodb and 1 file by partition, I have:

  • Table 1 partitioned by date
  • Table 2 that counts the entries of table 1 by date

Table 1 is filled by a software, and Table 2 is filled by triggers on Table 1, as below:

CREATE TRIGGER tgi_table1 AFTER INSERT ON table1 FOR EACH ROW INSERT INTO table2 values ( NEW.date, 1 ) ON DUPLICATE KEY UPDATE count = count+1;
CREATE TRIGGER tgd_table1 AFTER DELETE ON table1 FOR EACH ROW UPDATE table2 SET count = count-1;

Also, there is a script that runs periodically and execute DROP PARTITION on the old partitions of table1.

My question is: will these DROP PARTITION activate the trigger tgd_table1?

Thank you.

Solution

  • No, it wont use DELETE query for deleting records. It is dropping the data in a different way. Here is the explanation from mysql docs:

    DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.