Description:
When table "A" has a FK set to ON DELETE CASCADE,
and you delete a record,
and this record deletes another record in table "B",
and "B" has a BEFORE DELETE trigger,
B's trigger is not triggered.
How to repeat:
# First create a database for testing
CREATE DATABASE `debug`;
USE `debug`;
# Create table A
CREATE TABLE `debug`.`A` (
`a_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`a_content` VARCHAR( 100 ) NOT NULL
) ENGINE = INNODB;
# Create table B
CREATE TABLE `debug`.`B` (
`b_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`a_id` INT NOT NULL ,
`b_content` VARCHAR( 100 ) NOT NULL ,
INDEX ( `a_id` )
) ENGINE = INNODB;
# Add FK to B
ALTER TABLE `B` ADD FOREIGN KEY ( `a_id` ) REFERENCES `debug`.`A` (`a_id`)
ON DELETE CASCADE ON UPDATE CASCADE;
# Create BEFORE DELETE trigger on B
DELIMITER $$
CREATE TRIGGER `B_bu` BEFORE DELETE ON `B`
FOR EACH ROW BEGIN
call `Hey, you tried to delete me`();
END $$
DELIMITER ;
# Insert some values in A
INSERT INTO `A` (`a_content`) VALUES ('first'), ('second');
# Test inserts
SELECT * FROM A;
/* Must show this:
a_id a_content
------------------
1 first
2 second
*/
# Insert some values in B
INSERT INTO `B` (`b_id`, `a_id`, `b_content`) VALUES (NULL, '1', 'this will be deleted'), (NULL, '2', 'this will not be deleted');
# Test inserts
SELECT * FROM B;
/* Must show this:
b_id a_id b_content
---------------------------
1 1 first
2 2 second
*/
# Delete something from A
DELETE FROM A WHERE a_id = 1;
/* Now it should not let you delete, and should send a message saying that
you called a non-existing procedure, and should rollback any changes,
but it just proceeds deleting rows in A and B */
# Check current status of tables
SELECT * FROM A NATURAL JOIN B;
/*
Shows this:
a_id a_content b_id b_content
--------------------------------------------------
2 second 2 this will not be deleted
*/
Description: When table "A" has a FK set to ON DELETE CASCADE, and you delete a record, and this record deletes another record in table "B", and "B" has a BEFORE DELETE trigger, B's trigger is not triggered. How to repeat: # First create a database for testing CREATE DATABASE `debug`; USE `debug`; # Create table A CREATE TABLE `debug`.`A` ( `a_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `a_content` VARCHAR( 100 ) NOT NULL ) ENGINE = INNODB; # Create table B CREATE TABLE `debug`.`B` ( `b_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `a_id` INT NOT NULL , `b_content` VARCHAR( 100 ) NOT NULL , INDEX ( `a_id` ) ) ENGINE = INNODB; # Add FK to B ALTER TABLE `B` ADD FOREIGN KEY ( `a_id` ) REFERENCES `debug`.`A` (`a_id`) ON DELETE CASCADE ON UPDATE CASCADE; # Create BEFORE DELETE trigger on B DELIMITER $$ CREATE TRIGGER `B_bu` BEFORE DELETE ON `B` FOR EACH ROW BEGIN call `Hey, you tried to delete me`(); END $$ DELIMITER ; # Insert some values in A INSERT INTO `A` (`a_content`) VALUES ('first'), ('second'); # Test inserts SELECT * FROM A; /* Must show this: a_id a_content ------------------ 1 first 2 second */ # Insert some values in B INSERT INTO `B` (`b_id`, `a_id`, `b_content`) VALUES (NULL, '1', 'this will be deleted'), (NULL, '2', 'this will not be deleted'); # Test inserts SELECT * FROM B; /* Must show this: b_id a_id b_content --------------------------- 1 1 first 2 2 second */ # Delete something from A DELETE FROM A WHERE a_id = 1; /* Now it should not let you delete, and should send a message saying that you called a non-existing procedure, and should rollback any changes, but it just proceeds deleting rows in A and B */ # Check current status of tables SELECT * FROM A NATURAL JOIN B; /* Shows this: a_id a_content b_id b_content -------------------------------------------------- 2 second 2 this will not be deleted */