Setting NEW.column in "before update" trigger sets values from previous trigger call

Question

I'm trying to create a before update trigger for a table. Basically, on each row update I need to set additional column on the same row on the same table.

However, for some reason, each trigger call results in setting values from a previous trigger call.

Here's the code for my trigger:

create or replace function test_trigger_func() returns trigger as $$
    declare
        v_department  text;
    begin
        select department.name into v_department
        from salesprofile
        left join (
           select department.name from salesprofile join department on department.id = (
               salesprofile.solutionscreteria #>> '{departments, 0}'
           )::int4 where salesprofile.id = NEW.id
        ) department on true
        where salesprofile.id = NEW.id
        group by department.name;
        
        NEW.department = v_department;
        
        raise notice 'DEP: %', v_department;
        raise notice 'NEW DEP: %', NEW.department;
        
        return NEW;
    end;
$$ language plpgsql;

drop trigger if exists test_trigger on salesprofile;

create trigger test_trigger before update on salesprofile
for each row execute procedure test_trigger_func();

The select statement inside the test_trigger_func function works correctly when run outside the function. But the raise notice statements show incorrect (previous) values when the select is called from inside the test_trigger_func function.

The salesprofile.solutionscreteria #>> '{departments, 0}' statement contains the id for the row in the department table. And I'm trying to set the department column on the salesprofile table row from the "department".name on each salesprofile row update (by modifying NEW.department = ...).

---

The behaviour I get:

• the select statement is perfectly fine and works as expected (when called as is, outside the function).

• when I make the very first update to the salesprofile row, the trigger sets the department column as NULL (the column does not get updated at all);

• when I make the second update to the salesprofile row, the trigger sets the department column as the value that I was trying to set on the first update;

• when I make the third update to the salesprofile row, the trigger sets the department column as the value that I was trying to set on the second update;

• and so on...

• if I put the incorrect value to the salesprofile.solutionscreteria #>> '{departments, 0}' value, the first trigger update will not cause any errors.

• and then if I set the correct value after that, the trigger will fire with an error (caused by the previous trigger call with incorrect value).

---

I don't get how and why this is happening and I do hope I'm explaining the behaviour in an intelligible way.

Is this the expected behaviour for potgresql triggers? If not, then could you explain what's happening and how to make it work correctly?

Answer 1

It is a BEFORE trigger so it occurs before the salesprofile table has the NEW data. I'm not entirely following but I'm go to say that this salesprofile.solutionscreteria #>> '{departments, 0}' is using the existing(previous) row not the update data the trigger is running on. Try it with NEW.solutionscreteria.