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: http://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 ?
