CODEWITHSUNDEEP
mysqltriggersdatabase-partitioning
Matthieu.V
Matthieu.V

Reputation: 183

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 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.

Upvotes: 2

Views: 373

Answers (1)

abeyaz
abeyaz

Reputation: 3164

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.

Upvotes: 3

Related Questions