0

The table structure is:

CREATE TABLE `tags` (
 `idtag` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `parent` int(10) unsigned DEFAULT NULL,
 `name` varchar(45) DEFAULT NULL,
 `value` text COLLATE utf8_polish_ci,
 `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`idtag`),
 UNIQUE KEY `idx_parent_name` (`parent`,`name`), 
 KEY `idx_parent` (`parent`),
 CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `tags` (`idtag`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

This is going to be something like an XML structure. Each tag can have its parent (or NULL in top level) and I want to store when the tag was last modified, for caching purposes. The UNIQUE index is to prevent having two same sub-tags within a tag.

Because when I change/add/delete a sub-tag, the parent tag is logically also updated, I wanted to cascade the new TIMESTAMP (field modified) update in all parent tags, so I created a trigger:

CREATE TRIGGER `tags_AINS` AFTER INSERT ON `tags` FOR EACH ROW
  UPDATE `tags` SET `modified` = CURRENT_TIMESTAMP() WHERE `idtag` = NEW.parent;

(and the same code for AFTER UPDATE and AFTER DELETE triggers)

I hoped it would be possible to update all parent tags in cascade way.

This is however not possible in MySQL (eg. see this answer). I've also tried with BEFORE INSERT trigger, but the error is the same.

So my idea is that I will create another table to store timestamps, but I don't know, how to do this recursively (setting a trigger on this another table leads to the same issue).

Is it possible to achieve what I want?

(The database will work with PHP, and I can perform this check there, but there will be no sense for caching if I need to read all sub-tags from the db)

Community
  • 1
  • 1
Voitcus
  • 4,463
  • 4
  • 24
  • 40

1 Answers1

0

I actually think you could use a trigger and update them with something like this Something like:

CREATE TRIGGER `tags_AINS` AFTER INSERT ON `tags` FOR EACH ROW
UPDATE tags SET modified=NOW() WHERE idtag IN(
(SELECT @pv:=NEW.parent) WHERE idtag=@pv))

If that still doesn't work maybe look into using a stored procedure?

Instead of calling "INSERT INTO tags ..." You could create a stored procedure and call it like "CALL update_tags(...)"

Your stored procedure could handle the logic for cascaded updates.

James Greene
  • 52
  • 5
  • Check out this question for more ideas on how to do a recursive query http://stackoverflow.com/questions/28363893/mysql-select-recursive-get-all-child-with-multiple-level/28366310 – James Greene May 12 '15 at 20:54
  • After you nail down the correct recursive query I think using the IN operator will be the key :) – James Greene May 12 '15 at 20:56