Re: AFTER UPDATE trigger updating other records - Mailing list pgsql-general

On 01/24/2018 09:45 PM, Ian Harding wrote:

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;

Was there supposed to be results shown for the above queries?

-- 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;

Have you verified that this condition:

WHERE OLD.areapath @> areapath;

is actually being met?

--
Adrian Klaver
adrian.klaver@aklaver.com