AFTER UPDATE trigger updating other records

[Ian Harding <harding.ian@xxxxxxxxx>]

I have a conditional after update trigger on a table that issues an update statement on the same table.  The trigger does not fire recursively, and I know the rows are being updated, but the update is not happening in the same way the statement does when run from outside the trigger. 

--8<----------

create extension if not exists ltree;
 
create table area (
        areaid serial primary key,
        parentid int null references area (areaid),
        areapath ltree not null unique);

insert into area (areapath) values ('Top');

insert into area (parentid, areapath) values (1,'Top.Foo');
insert into area (parentid, areapath) values (1,'Top.Bar');
insert into area (parentid, areapath) values (3,'Top.Bar.Blah');
insert into area (parentid, areapath) values (4,'Top.Bar.Blah.Scooby');

select areaid, parentid, areapath from area order by areapath;

CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
$$
BEGIN
  IF TG_OP = 'UPDATE' THEN
        IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0)) THEN
            UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1)
            WHERE OLD.areapath @> areapath;
        END IF;
  END IF;

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

CREATE TRIGGER trig01_update_area_node_path AFTER UPDATE OF parentid ON area FOR EACH ROW
   EXECUTE PROCEDURE trig_areapath_u();

update area set parentid = 2 where areaid = 4;

select areaid, parentid, areapath from area order by areapath;

-- This is not what I expect to see.  I have even tried running the update

-- unrestricted from within the trigger but I get the same result.  From

-- outside the trigger I run the update unrestricted...

UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1) where parentid is not null;

-- And I see what I expected.

select areaid, parentid, areapath from area order by areapath;

--------->8-----

I know this is simple, but I can't see it.

Thank you!

- Ian