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.

友情链接