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;

Reply via email to