On Wednesday, January 24, 2018, Ian Harding <
harding.ian@gmail.com> wrote:
-- 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.
I'd start thinking that the function that the trigger is executing is not the one that I am editing. Adding raise notice to the function should give you some confirmation as to what is firing.
Are rows beside the one your are updating not changing or not changing correctly.
A self-contained example script would help you with isolation and us if you still cannot figure it out after writing one.
David J.