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;