[MDEV-19402] Invoke triggers for foreign key cascade actions - Jira

XML

Word

Printable

The documentation does indeed clearly explain the existing behavior:

Triggers are not activated by foreign key actions.

However I still expect triggers to be activated even by foreign key actions, and the documentation should be updated to remove that limitation.

This has been a longstanding issue in MySQL as well: https://bugs.mysql.com/bug.php?id=11472

It can be reproduced with the following code:

DROP TABLE IF EXISTS parent, child;

CREATE TABLE parent (

    id INT PRIMARY KEY

) ENGINE=INNODB;

CREATE TABLE child (

    id INT PRIMARY KEY,

    parent_id INT,

    INDEX (parent_id),

    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

) ENGINE=INNODB;

CREATE TRIGGER trigger_on_child BEFORE DELETE ON child

    FOR EACH ROW SET @counter = @counter + 1;

INSERT INTO parent VALUES (1),  (2),  (3);

INSERT INTO child VALUES (1, 2), (2, 2), (3, 3);

SET @counter=0;

DELETE FROM parent WHERE id = 2;

SELECT @counter;

The actual output will be:

+----------+

| @counter |

+----------+

|        0 |

+----------+

But the expected output is;

+----------+

| @counter |

+----------+

|        1 |

+----------+

duplicates

MDEV-12302 Execute triggers for foreign key updates/deletes

relates to

MDEV-16417 Store Foreign Key metadata outside of InnoDB

MDEV-22602 WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.