Thursday, May 5, 2016

Trigger calls in cascade deleting

By Hường Hana 1:30 AM , , , Leave a Comment

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

4 Answers

Answers 1

From http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

Note: Currently, cascaded foreign key actions do not activate triggers.

In other words you can not use the trigger with cascaded deleting.

Answers 2

You can use all triggers solution.

Refer to: MySQL: Trigger not activated on delete cascade

Answers 3

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.

Answers 4

CREATE TABLE doc ( docID INTEGER NOT NULL AUTO_INCREMENT, langCode CHAR(2) NOT NULL, title VARCHAR(32), PRIMARY KEY (docID, langCode) ) Type=InnoDB;  CREATE TABLE author ( authorID INTEGER NOT NULL AUTO_INCREMENT, docID CHAR(2) NOT NULL, name VARCHAR(32), PRIMARY KEY (authorID), FOREIGN KEY (docID) REFERENCES doc(docID) ON DELETE CASCADE ON UPDATE CASCADE ) Type=InnoDB;
If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment