after update trigger not firing based on condition in postgres

Question

I have a trigger in a table in postgres, whenever a column called intended_remediation_date is updated. If after the update this column is not null and if another column called binary_status = inactive, then I want to update the value of my target column , which I call remediated to true, otherwise it should be false.

Here is my function and trigger:

create or replace function set_remediated_status() returns trigger as $$
begin
if new.intended_remediation_date is not null and new.binary_status = 'inactive' then
new.remediated := true;
else
new.remediated := false;
end if;
return new;
end;
$$ language plpgsql;

---

create or replace trigger update_remediated_status
after update of intended_remediation_date,binary_status on evergreen
for each row
when (new.intended_remediation_date is not null and new.binary_status = 'inactive')
execute function set_remediated_status();

The code works, however it fails silently .... suppose the columns looks like that before update:

intended_remediation_date   binary_status   remediated
NULL                        active           FALSE

Then I update :

update evergreen
set 
intended_remediation_date = '2025-03-01',
binary_status = 'inactive'
where id = 1;

intended_remediation_date   binary_status   remediated
2025-03-01                      inactive        FALSE

So apparently the trigger is not firing....

Answer 1

See "after update". When the update is done, you set the variable for a column. But the update is already done. Change the trigger to fire "before update" and everything works fine.