Tree data in MySql database table
- by Robert Koritnik
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?
