Re: AFTER UPDATE trigger updating other records

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?

> 
> --------->8-----
> 
> I know this is simple, but I can't see it.
> 
> Thank you!
> 
> - Ian


-- 
Adrian Klaver
adrian.klaver@aklaver.com