Trigger calls in cascade deleting

I have table "A" in MySQL. It has some references with cascade deleting to some other tables ("B", "C", "D" ...). I need to use a trigger when something deletes from "A". This trigger works when I delete records from "A" directly. But it doesn't work with cascade deleting. Does any version of MySQL exist where my trigger will work with cascade deleti开发者_开发百科ng? Or, maybe, there is another way to call

---

From http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

Cascaded foreign key actions do not activate triggers

In other words, you cannot use the trigger with cascaded deleting.

Also see related bugs:

• MySQL: https://bugs.mysql.com/bug.php?id=11472
• MariaDB: https://jira.mariadb.org/browse/MDEV-19402

---

To summarize the answers from @Niel de Wet and @Browny Lin:

1. Sadly cascaded deletes do not activate triggers in MySQL.
2. One solution is to not use cascading deletes but instead implement the automatic delete via another trigger.

---

Let me share how I've been "fixing" this issue from the first day I discovered it existed. I copy the trigger from the cascaded table into the first table that's deleted directly. It just works.

A lot of times it's a matter of copy/paste, and occasionally it requires extensive re-writing of the code.

The best part is, when Oracle finally fixes this Bug you only have remove the trigger code from the said table. Voila!

---

Yeah unfortunately this is a bug. But we do can do something to achieve what we need. Instead of defining foreign key for CASCADE update or delete you can achieve that by making a trigger.

For example if you have 2 tables categories and subcategories and you have a foreign key assigned to subcategories so on delete a category its subcategories should get deleted automatically. You can achieve the same functionality without foreign key and using triggers like shown below:

CREATE TRIGGER categories_delete AFTER DELETE ON categories
    FOR EACH ROW BEGIN
        DELETE FROM subcategories WHERE category_id = OLD.id;
    END;

This will now let you do other triggers work just fine.

I hope that's helpful.