[MDEV-19402] Invoke triggers for foreign key cascade actions Created: 2019-05-07  Updated: 2023-11-05  Resolved: 2023-11-05

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Adrien Crivelli Assignee: Unassigned
Resolution: Duplicate Votes: 1
Labels: trigger

Issue Links:
Duplicate
duplicates MDEV-12302 Execute triggers for foreign key upda... Confirmed
Relates
relates to MDEV-16417 Store Foreign Key metadata outside of... In Review
relates to MDEV-22602 WITHOUT OVERLAPS constraint is ignore... Closed

 Description   

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 |
 
+----------+


Generated at Thu Feb 08 08:51:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.