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;