MySQL trigger after update with composite primary key is failing-CodePudding

I want to save changes into my remote table for synchronization with an Android application.

The table with the data is defined as:

CREATE TABLE `lh_psalm_join` (
  `groupFK` int(11) NOT NULL,
  `readingFK` int(11) NOT NULL,
  `theOrder` int(11) NOT NULL,
  `themeFK` int(11) DEFAULT NULL,
  `epigraphFK` int(11) DEFAULT NULL,
  `thePart` int(11) DEFAULT NULL,
  PRIMARY KEY (`groupFK`,`readingFK`),
  KEY `readingFK` (`readingFK`),
  KEY `epigraphFK` (`epigraphFK`),
  KEY `themeFK` (`themeFK`),
  CONSTRAINT `lh_psalm_join_ibfk_1` FOREIGN KEY (`groupFK`) REFERENCES `lh_psalmody_join` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_2` FOREIGN KEY (`readingFK`) REFERENCES `lh_psalm` (`psalmID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_3` FOREIGN KEY (`epigraphFK`) REFERENCES `lh_epigraph` (`epigraphID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_4` FOREIGN KEY (`themeFK`) REFERENCES `lh_theme` (`themeID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

And this is the table for sincronization:

CREATE TABLE `sync_lh_psalm_join` (
  `groupFK` int(11) NOT NULL,
  `readingFK` int(11) NOT NULL,
  `crud` char(1) DEFAULT NULL,
  `lastUpdate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`groupFK`,`readingFK`),
  CONSTRAINT `sync_lh_psalm_join_ibfk_1` FOREIGN KEY (`groupFK`, `readingFK`) REFERENCES `lh_psalm_join` (`groupFK`, `readingFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

When a row is inserted or updated in lh_psalm_join, I want to save a reference in sync_lh_psalm_join to do the synchronization by checking for the lastUpdate field.

For this I have defined this TRIGGER:

DROP TRIGGER IF EXISTS `lh_psalm_join_after_update`;
DELIMITER $$
CREATE TRIGGER `lh_psalm_join_after_update` AFTER UPDATE ON `lh_psalm_join` FOR EACH ROW
BEGIN
  DECLARE countRows INT(11) DEFAULT 0;
  SELECT 
    COUNT(*) 
  FROM `sync_lh_psalm_join` 
  WHERE 
    `groupFK`=NEW.`groupFK` AND 
    `readingFK`=NEW.`readingFK` 
  INTO countRows;
  IF countRows>0 THEN
    UPDATE `sync_lh_psalm_join` SET 
      `groupFK`=NEW.`groupFK`,
      `readingFK`=NEW.`readingFK`,
      `crud`='u', 
      `lastUpdate`=CURRENT_TIMESTAMP 
    WHERE 
      `groupFK`=OLD.`groupFK` AND 
      `readingFK`=OLD.`readingFK`;
  ELSE
    INSERT INTO `sync_lh_psalm_join` 
      (`groupFK`,`readingFK`,`crud`) 
    VALUES
      (NEW.`groupFK`,NEW.`readingFK`,'u');
  END IF;
  CALL spUpdateSyncStatus('lh_psalm_join');
END$$
DELIMITER ;

Since there might already be a record with the primary key, my idea is to change that record to the new values of the primary keys, in case of update, and change the value of the lastUpdate column to CURRENT_TIMESTAMP.

But it is not working, if I try an UPDATE in lh_psalm_join of some row whose primary key already exists in sync_lh_psalm_join, it gives me this error:

Cannot delete or update a parent row: a foreign key constraint fails (c39b075_deiverbu.sync_lh_psalm_join, CONSTRAINT sync_lh_psalm_join_ibfk_1 FOREIGN KEY (groupFK, readingFK) REFERENCES lh_psalm_join (groupFK, readingFK))

The error persists if I change the WHERE to:

    WHERE 
      `groupFK`=NEW.`groupFK` AND 
      `readingFK`=NEW.`readingFK`;

What is wrong with my approach?

CodePudding user response：

The error has nothing to do with your trigger. You'd get the same error if you had no trigger, and you try to update a primary key that another table depends on.

The default action for a foreign key includes ON UPDATE RESTRICT, which is the same as ON UPDATE NO ACTION for InnoDB. This means it's an error if someone tries to update the primary key that a child row references. The update is blocked, and the caller gets an error.

This of this like you're reading a newspaper and your partner is looking over your shoulder. You go to turn the page, and your partner puts their hand out to block you from doing that, because they're still reading the current page. :-)

Other referential actions include ON UPDATE CASCADE, which seems to be what you want. It will copy the new primary key values to the child table atomically. The values will be changed in the parent row and the child row concurrently, and there is no error.

Read https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-referential-actions for more information on these options.

You must declare the foreign key this way:

CREATE TABLE `sync_lh_psalm_join` (
  ...
  CONSTRAINT `sync_lh_psalm_join_ibfk_1` FOREIGN KEY (`groupFK`, `readingFK`) 
    REFERENCES `lh_psalm_join` (`groupFK`, `readingFK`) ON UPDATE CASCADE
);

So for your trigger, it can be a lot simpler if you use INSERT...ON DUPLICATE KEY UPDATE.

CREATE TRIGGER `lh_psalm_join_after_update` AFTER UPDATE ON `lh_psalm_join` FOR EACH ROW
BEGIN
  INSERT INTO `sync_lh_psalm_join` 
    (`groupFK`,`readingFK`,`crud`) 
  VALUES
    (NEW.`groupFK`,NEW.`readingFK`,'u')
  ON DUPLICATE KEY UPDATE `crud`='u', lastUpdate=CURRENT_TIMESTAMP;
  CALL spUpdateSyncStatus('lh_psalm_join');
END$$

The key columns should be updated automatically by virtue of declaring the foreign key ON UPDATE CASCADE.

Read https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html for more information on INSERT...ON DUPLICATE KEY UPDATE.