Mysql – workaround for lack of support of cascade triggers with MySQL

foreign keyMySQLtriggerupdate

As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a workaround for this.

I'm currently using triggers to do something like this:

CREATE TRIGGER `Set_field1_on_update` BEFORE UPDATE ON `mytable`
FOR EACH ROW BEGIN
    SET NEW.field1 =  CONCAT(NEW.myforeignkey,'whatever');
END

Which works fine when myforeignkey is changed directly on mytable but not when it is updated with ON UPDATE CASCADE via its referenced table.

Is there anyway to achieve the above functionality (using something else than triggers if necessary) on cascaded foreign key actions?

Best Answer

The workaround, if that's the right word for it, seems like it would be to make the same changes to the child table in an AFTER UPDATE trigger on the parent table, containing the same logic as the BEFORE UPDATE trigger on the child table, wrapped inside a test to only do anything if the primary key in the parent table has actually changed, which would have caused a cascaded update.

DELIMITER $$
CREATE TRIGGER parent_after_update AFTER UPDATE ON parent_table FOR EACH ROW
BEGIN

# not using null-safe <=> comparison since PK cannot be null

IF NEW.primary_key != OLD.primary_key THEN

  UPDATE child_table ct
     SET /* the same logic that's in the before update trigger on the child table */
   WHERE ct.parent_primary_key = NEW.primary_key;

END IF;
END $$
DELIMITER ;

If primary_key on parent_table just now changed, then by definition, all of the rows child_table with the new value in their parent_primary_key column must have just been updated via a cascaded update, because until that value exists in parent_table.primary_key, there could not have been any rows already existing with the new value in child_table.parent_primary_key ... until the parent table's primary_key was updated just now, those rows could not possibly have existed, since they would have violated the referential constraint of the foreign key (referencing a nonexistent value).

Of course, I'm using "primary key" here when in reality the foreign key could reference any superkey of the parent table.

However, this calls into question what you're really using the cascaded updates to accomplish in the first place and whether that application of cascaded updates constitutes a sound design. You seem to be deriving another column from the value, and storing that derived column, and both of these seem like less-than-ideal practices.

Normally, a cascaded update, at least in principle if not in fact, seems like something that is not a change to the data, as an update changing the child table directly would be. "These" child records reference "that" row in the parent table. After a cascaded update, they still do, and nothing has changed, only the key is different.

Related Question