Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19402

Invoke triggers for foreign key cascade actions

    XMLWordPrintable

    Details

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              PowerKiKi Adrien Crivelli
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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