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`
    SET NEW.field1 =  CONCAT(NEW.myforeignkey,'whatever');

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?

  • That would seem to me to ve a reason not to allow cascade update
    – HLGEM
    Commented Apr 10, 2013 at 16:02
  • I need cascade update (for other reasons).
    – Max
    Commented Apr 10, 2013 at 16:21
  • Is this MySQL only? Would an ON UPDATE CASCADE event fire the ON UPDATE trigger on mytable in Postgresql?
    – VH-NZZ
    Commented Sep 23, 2015 at 11:00

1 Answer 1


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.

CREATE TRIGGER parent_after_update AFTER UPDATE ON parent_table FOR EACH ROW

# 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 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.

  • I would use it with a before trigger not after as in cascade there is nothing after, it's cascaded... but other than that it looks good. Commented Oct 1, 2019 at 22:00
  • @RomanRabinovich I wrote this over six years ago, so I admit I don't actually remember the details, and reading it now, it is not fully clear to me what problem was really being solved. Commented Oct 1, 2019 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.