Trigger calls in cascade deleting

Question

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 deleting? Or, maybe, there is another way to call

This is an argument for doing the logic in application code, not a complex setup of Triggers and Cascading. — Rick James, Apr 30 '16 at 18:56

score 39 · Answer 1 · edited May 07 '19 at 00:18

39

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

edited May 07 '19 at 00:18

PowerKiKi

4,539
4
39
47

answered May 23 '11 at 09:56

Niel de Wet

7,806
9
63
100

63

i hate mysql more every day – Neil McGuigan Apr 16 '12 at 19:47
4

I just found this problem, and it's supremely dumb :-( – Nick Bedford Apr 04 '15 at 05:48
@NeilMcGuigan `mysql 5.7.9` not yet supported this Property? – ops Apr 28 '16 at 08:38
@NeilMcGuigan are you saying that 5.7.9 does support this properly? – ahsteele Sep 12 '16 at 20:41
@ahsteele I made that comment 4 years ago, and don't use MySQL anymore...so, I don't know – Neil McGuigan Sep 12 '16 at 21:19
3

Sorry I meant to reply @yonessafari. To answer the question its still a bug: https://bugs.mysql.com/bug.php?id=11472 – ahsteele Sep 12 '16 at 21:37

score 8 · Answer 2 · answered Jan 30 '15 at 01:02

8

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

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

answered Jan 30 '15 at 01:02

jcoffland

5,238
38
43

1

using another trigger for deleting is not a proper approach. because if I have a trigger on tableA and delete a row in tableB, and also If I have a trigger aon tableB for updating tableA, then a collision will occur. – Shafizadeh Sep 18 '15 at 21:05

score 5 · Answer 3 · answered Dec 30 '16 at 13:22

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!

score 1 · Answer 4 · answered Feb 15 '20 at 11:38

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.

Trigger calls in cascade deleting

4 Answers4

Linked

Related