Cascade Trigger Not Firing

From: Judy Loomis <hoodie(dot)judy(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Cascade Trigger Not Firing
Date: 2019-09-13 19:03:45
Message-ID: CADoG1K1zHEQJh8xgpy3kYMXLc8X8W6md2WafJkFPcp-CsWYsng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-09-13 19:08:49 Re: Cascade Trigger Not Firing
Previous Message Melvin Davidson 2019-09-13 18:33:07 Re: problems importing from csv