CODEWITHSUNDEEP
mysql
merlin
merlin

Reputation: 2917

How to update timestamp with mysql foreign key constraint on update cascade?

I have two tables:

  1. items
  2. items_details

If tabel 2 updates it's content, I want the timestamp (change on update) within tabel 1 updated.

Therefore I definded in MySql foreign key constraint on update cascade within table 2. This somehow has no effect at all.

How can the timestamp of table 1 be updated if the content in table 2 changes?

Upvotes: 2

Views: 888

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522109

You could use an after update trigger:

DELIMITER //
CREATE TRIGGER items_details_after_update
AFTER UPDATE
ON items_details FOR EACH ROW
BEGIN
    UPDATE items
    SET timestamp = CURRENT_TIMESTAMP
    WHERE NEW.item_id = id;
END; //
DELIMITER ;

This answer assumes that there exists a column item_id in the item_details table, which is a foreign key pointing to a primary key id column in the parent items table.

Upvotes: 2

Related Questions