Triggers are not executed when a field is changed via NEW

When changing a field in a row through a NEW record in a BEFORE trigger, how to execute AFTER triggers?

Example:

CREATE TABLE IF NOT EXISTS table1 (
    id serial PRIMARY KEY,
    parent_id integer,
    stime timestamptz NOT NULL DEFAULT NOW(),
    etime timestamptz DEFAULT NULL,
    time_spent real DEFAULT NULL,
    rate NUMERIC(20, 4) DEFAULT NULL,
    cost NUMERIC(20, 4) DEFAULT NULL
);

CREATE OR REPLACE FUNCTION trigger1()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger1{%, %, %, %} runned!', TG_WHEN, TG_OP, TG_TABLE_NAME, COALESCE(NEW.id, OLD.id);
    NEW.time_spent = EXTRACT(EPOCH FROM (NEW.etime - NEW.stime));
    NEW.cost := NEW.time_spent * NEW.rate; -- field "cost" has been changed!
    RETURN NEW;
END;
$BODY$;
DROP TRIGGER IF EXISTS before_100_calc_cost ON table1;
CREATE TRIGGER before_100_calc_cost 
  BEFORE INSERT OR UPDATE OF stime, etime ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger1();

The second trigger trigger2, which for some reason is not executed even though trigger1 alters the cost field:

CREATE OR REPLACE FUNCTION trigger2()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger2{%, %, %, %} runned!', TG_WHEN, TG_OP, TG_TABLE_NAME, COALESCE(NEW.id, OLD.id);
    create table if not exists debug_(
      id int generated by default as identity primary key
      , ts timestamptz default clock_timestamp()
      , comment text);
    insert into debug_(comment)
      values(format('tg_op:"%s",new:"%s",old:"%s"'
                    ,tg_op,to_jsonb(new)::text,to_jsonb(old)::text));
    IF (TG_OP <> 'DELETE') THEN RETURN NEW; ELSE RETURN OLD; END IF;
END;
$BODY$;
DROP TRIGGER IF EXISTS after_105_calc_parent_cost ON table1;
CREATE TRIGGER after_105_calc_parent_cost 
  AFTER UPDATE OF cost ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger2();

test case:

INSERT INTO table1 (stime) VALUES (NOW() - INTERVAL '1 hour');
UPDATE table1 SET etime = NOW(); -- trigger "trigger2" did not start!

When trigger1 is executed and the cost field is changed, trigger trigger2 does not fire.

Solution

I'm guessing you expected the update of cost to fire whenever anything touches that field in any way, updating it, which isn't the case.

Initial INSERT fires both triggers.
UPDATE only fires trigger1. Although it does attempt to change the cost field, it's not among the target columns of the UDPATE statement and trigger2 is defined to only fire if it is (update or insertof cost). Quoting the CREATE TRIGGER doc:
For UPDATE events, it is possible to specify a list of columns using this syntax:
```
UPDATE OF column_name1 [, column_name2 ... ]
```
The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command or if one of the listed columns is a generated column that depends on a column that is the target of the UPDATE.

There's no way to catch it by comparing NEW vs OLD either because the first trigger changes it from a null to a still a null. The rate field is left without a default value and the insert, the update or the trigger on either event aren't trying to fill it, so it remains a null that nullifies the expression meant to alter the cost.

If you also fix the cost calculation, you can set up trigger2 to fire whenever the event does end up changing its value by using the trigger..when(condition):

condition
A Boolean expression that determines whether the trigger function will actually be executed. If WHEN is specified, the function will only be called if the condition returns true. In FOR EACH ROW triggers, the WHEN condition can refer to columns of the old and/or new row values by writing OLD.column_name or NEW.column_name respectively. Of course, INSERT triggers cannot refer to OLD and DELETE triggers cannot refer to NEW.

_{demo at db<>fiddle}

CREATE TRIGGER after_104_calc_parent_cost 
  AFTER UPDATE ON table1 
  FOR EACH ROW 
  WHEN (NEW.cost is distinct from OLD.cost)
  EXECUTE PROCEDURE trigger2();