4 | 3 | Top.Bar.Blah
5 | 4 | Top.Bar.Blah.Scooby
barf$# UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1)
barf$# WHERE OLD.areapath @> areapath;
barf=# update area set parentid = 2 where areaid = 4;
OLD.areapath = Top.Bar.Blah
When its Top.Bar.Blah.Scooby 's turn to be updated its parent is 4 which has a value of "Top.Bar.Blah" and so nothing happens. It doesn't matter whether row 4 or row 5 occurs first - when multiple rows are updated your subselect presents the exact same data to each row and it doesn't take into account updates applied to other rows made concurrently.
Top.Bar.Blah 's parent was changed to 2 so it does go from "Top.Bar.Blah" to "Top.Foo.Blah"
When you then run your update manually row 5 sees the newly committed areapath value for row 4 and now affects the change to "Top.Foo.Blah.Scooby"
IOW, cascading updates are not possible (regardless of whether you are doing them in a trigger or manually). If you had a three-deep parent-child hierarchy to change in your example you would have seen that your example script would only have corrected the first two levels.
Probably your trigger will need to capture (SELECT areapath .. WHERE id = NEW.parentid) and then use that constant in your SET clause.
David J.
