Tree data in MySql database table

Question

I have a table that uses Adjacency list model for hierarchy storage. My most relevant columns in this table are therefore:

ItemId // is auto_increment
ParentId
Level
ParentTrail // in the form of "parentId/../parentId/itemId"

then I created a before insert tigger, that populates columns Level and ParentTrail. Since the last column also includes current item's ID I had to use a trick in my trigger because auto_increment columns are not available in the before insert trigger. So I get that value from the information_schema.tables table.

All works fine, until I try to write an update trigger, that would update my item and its descendants when the item changes its parent (ParentId has changed). But I can't make an update on my table inside the update trigger. All I can do is to change current record's values but not other's.

I could use a separate table for hierarchy data, but that would mean that I would also have to create a view that would combine these two tables (1:1 relation) and I would like to avoid this is at all possible.

Is there a way to have all these in the same table so that these fields (Level and ParetTrail) set/update themselves automagically using triggers?

Answer 1

This sounds like it might be possible, but it would involve some heavy string matching etc. Wouldn't want to dig myself into it.

If you are willing to change your database model slightly and implement a Nested Set (scroll down on the page) this could probably be done easily by having the trigger update left-right values.