Cascade Trigger Not Firing
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;
Judy Loomis <hoodie.judy@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
Judy Loomis <hoodie.judy@gmail.com> 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 thelist
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
Judy Loomis <hoodie.judy@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
Judy Loomis <hoodie.judy@gmail.com> 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
On 9/13/19 8:07 PM, Judy Loomis wrote:
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.
I know this after the fact. Still, as a general rule the best place to
start when learning about a command is on it's respective page under here:
https://www.postgresql.org/docs/11/sql-commands.html
Further I usually go to the Notes after reading the synopsis, as Notes
is where the exceptions to the rules and gotchas are called out.
On Fri, Sep 13, 2019 at 4:03 PM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:Judy Loomis <hoodie.judy@gmail.com <mailto:hoodie.judy@gmail.com>>
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 wealways 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
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
On 9/13/19 8:07 PM, Judy Loomis wrote:
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.
I know this after the fact. Still, as a general rule the best place to
start when learning about a command is on it's respective page under here:
https://www.postgresql.org/docs/11/sql-commands.html
Further I usually go to the Notes after reading the synopsis, as Notes
is where the exceptions to the rules and gotchas are called out.
The only mention of this feature in 38.1 "Overview of Trigger Behavior"
is
UPDATE triggers can moreover be set to fire only if certain columns
are mentioned in the SET clause of the UPDATE statement.
which seems to me to be plenty specific enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.
regards, tom lane
On 9/14/19 9:54 AM, Tom Lane wrote:
[snip
The only mention of this feature in 38.1 "Overview of Trigger Behavior"
isUPDATE triggers*can* moreover be set to fire only if certain columns
are mentioned in the SET clause of the UPDATE statement.which seems to me to be plenty specific enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.
Given that the UPDATE "*can* ... be set to fire only if certain columns are
mentioned in the SET clause of the UPDATE statement", it logically follows
that the default behavior is something else (for example, if the field value
changes for whatever reason.
--
Angular momentum makes the world go 'round.
On Sat, 14 Sep 2019 10:00:18 -0500, Ron <ronljohnsonjr@gmail.com>
wrote:
On 9/14/19 9:54 AM, Tom Lane wrote:
[snipThe only mention of this feature in 38.1 "Overview of Trigger Behavior"
isUPDATE triggers*can* moreover be set to fire only if certain columns
are mentioned in the SET clause of the UPDATE statement.which seems to me to be plenty specific enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.Given that the UPDATE "*can* ... be set to fire only if certain columns are
mentioned in the SET clause of the UPDATE statement", it logically follows
that the default behavior is something else (for example, if the field value
changes for whatever reason.
But the default could be "any column mentioned", not necessarily any
value changed.
George