MySQL INSERT-SELECT multiple rows from versioned table

Question

I've got 2 versioned tables like this:

Items:

ID
rev_id
name
deleted

Subitems:

ID
rev_id
name
parent_id
deleted

What i understand from http://kristiannielsen.livejournal.com/6745.html that it is pretty easy and fast to get all the latest versions of my objects from my tables like this:

SELECT a.name, a.id, a.rev_id, a.deleted FROM Items a
INNER JOIN (SELECT id, MAX(rev_id) AS rev_id FROM Items GROUP BY id) b
ON (a.id= b.id AND a.rev_id b.rev_id)

but when a user deletes a record it would be cool if i could insert a new record with deleted is 1 as a new record. I found here: https://stackoverflow.com/a/4039781/672989 that i could insert something like this:

INSERT INTO table (id, rev_id, name, deleted)
SELECT id, rev_id, name, 1 FROM Items WHERE id = 1 ORDER BY rev_id DESC LIMIT 1

But if an item in the Items table gets deleted = 1, I would like to add new records with deleted = 1 for all the subitems where parent_id == items.id in the subitems table as well. what is the easies way to do this, is it possible with one query? And what if i even have another subsubitems table, how could i achieve the same there ?

Do you need to add new records or just update the existing ones? Why not just: UPDATE Subitems SET deleted=1 WHERE parent_id={$id} — Joe M., Apr 17 '12 at 19:42

score 0 · Answer 1 · answered May 07 '12 at 23:59

You need to create an "on insert" trigger that overrides the value for deleted if the parent has been so marked:

DELIMITER ;;

CREATE TRIGGER foo BEFORE INSERT ON Items FOR EACH ROW
  IF (SELECT deleted FROM Items WHERE id = NEW.parent_id) THEN
    SET NEW.deleted := 1;
  END IF;;

DELIMITER ;

MySQL INSERT-SELECT multiple rows from versioned table

1 Answers1