Re: AFTER UPDATE trigger updating other records

On Thu, Jan 25, 2018 at 2:33 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
> ​
>
> For the record:

CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
$$
DECLARE
ltree_parentpath ltree;
ltree_mypath ltree;
int_cnt int;
BEGIN
IF TG_OP = 'UPDATE' THEN
IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0)) THEN
-- Get the new parent path and save it
-- Get the old path for this item and save it
-- Replace the first X elements of the path for this and all
-- my children with the parent path

SELECT areapath
INTO ltree_parentpath
FROM area
WHERE areaid = NEW.parentid;

ltree_mypath := OLD.areapath;

UPDATE area SET areapath = ltree_parentpath ||
subpath(areapath, nlevel(ltree_parentpath) )
WHERE ltree_mypath @> areapath;

GET DIAGNOSTICS int_cnt = ROW_COUNT;
RAISE NOTICE 'Rows affected: %', int_cnt;
END IF;
END IF;

RETURN NULL;
END
$$
LANGUAGE 'plpgsql' VOLATILE;