Cascade Trigger Not Firing

[Judy Loomis]

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;

Re: Cascade Trigger Not Firing

[Judy Loomis]

I thought that might be the answer, but it's a pretty big hole when we're
using triggers for audit purposes on financial data.

I'm going to have to really look at all my BEFORE UPDATE triggers and make
sure we're not missing any more.

And I have to stop telling management that a trigger means we always know
when a value changes.

Thanks,
Judy

On Fri, Sep 13, 2019 at 2:08 PM Tom Lane  wrote:

> Judy Loomis  writes:
> > 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?
>
> Per the manual (NOTES section of the CREATE TRIGGER man page):
>
> A column-specific trigger (one defined using the UPDATE OF column_name
> syntax) will fire when any of its columns are listed as targets in the
> UPDATE command's SET list. It is possible for a column's value to
> change even when the trigger is not fired, because changes made to the
> row's contents by BEFORE UPDATE triggers are not
> considered. Conversely, a command such as UPDATE ... SET x = x ...
> will fire a trigger on column x, even though the column's value
> did not change.
>
> It's not really practical for trigger firings to depend on what other
> triggers did or might do --- you'd soon end up with circularities.
>
> regards, tom lane
>


-- 

--
*Judy Loomis*
469.235.5839

Re: Cascade Trigger Not Firing

[Judy Loomis]

At the very least that note about this behavior should be highlighted,
probably on the Trigger Behavior page and not buried in a bunch of notes on
the Create Trigger page.



On Fri, Sep 13, 2019 at 4:03 PM Tom Lane  wrote:

> Judy Loomis  writes:
> > I'm going to have to really look at all my BEFORE UPDATE triggers and
> make
> > sure we're not missing any more.
> > And I have to stop telling management that a trigger means we always know
> > when a value changes.
>
> Well, you can rely on that, just not like this.  Use an AFTER trigger
> (else, you can't be sure it fires after all the BEFORE triggers)
> and instead of triggering it with a column parameter, have it do
> something like "if old.col is distinct from new.col".
>
> Yeah, it's a bit slower that way, but there's no free lunch,
> especially if you don't trust your other triggers.  (Although,
> if you have so many triggers that that's a problem, I think you
> might have some other design issues.)
>
>     regards, tom lane
>


-- 

--
*Judy Loomis*
469.235.5839