Cascade Trigger Not Firing

[Judy Loomis <hoodie.judy@xxxxxxxxx>]

I have a trigger that updates a target column when some other columns change.

There is another trigger on the target column to update another table (the column can be changed in other ways besides the first trigger).

If I update the target column directly the expected trigger fires.

But if the 1st trigger changes the target column and it wasn't in the list of updated columns, the 2nd trigger doesn't fire.

Is this expected behavior? I thought that ANY change to the column would fire the trigger.

Note that I've got a work-around by making the first trigger an AFTER trigger and calling UPDATE instead of just changing NEW. But it was a while before we caught this and it's worrisome to me that a column can change without a trigger noticing.

Here's about the smallest example I could come up with:

-----------------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS table1(
  id SERIAL,
  total INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS table2(
  id SERIAL,
  t1_id INTEGER,
  col1 INTEGER DEFAULT 0,
  col2 INTEGER DEFAULT 0
);
CREATE OR REPLACE FUNCTION update_total()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
    RAISE WARNING '### in update_total: %',NEW;
    UPDATE table1
       SET total = NEW.col2
     WHERE id = NEW.t1_id;

    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION update_col2()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
    RAISE WARNING '**** in update_col2: %', NEW;
    NEW.col2 = NEW.col1 * 3;

    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER au_update_total
      AFTER UPDATE OF col2
      ON table2
      FOR EACH ROW
      EXECUTE PROCEDURE update_total();

CREATE TRIGGER biu_update_col2
      BEFORE INSERT OR UPDATE OF col1
      ON table2
      FOR EACH ROW
      EXECUTE PROCEDURE update_col2();

INSERT INTO table1 VALUES (DEFAULT, -99);
INSERT INTO table2 VALUES (DEFAULT, 1, 10, 10); -- fires col2 trigger
SELECT * FROM table1;
SELECT * FROM table2;

UPDATE table2 SET col2 = 99; -- fires total trigger
SELECT * FROM table1;
SELECT * from table2;

UPDATE table2 SET col1 = 5; -- ** only col2 trigger is fired; expected total trigger to fire ***
SELECT * FROM table1;
SELECT * from table2;

UPDATE table2 SET col1 = 3, col2 = col2; -- fires both triggers
SELECT * FROM table1;
SELECT * from table2;