Re: AFTER UPDATE trigger updating other records

On Thu, Jan 25, 2018 at 3:06 PM, Ian Harding <harding(dot)ian(at)gmail(dot)com> wrote:

>
> 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.
​