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@xxxxxxxxxxxxx> wrote:
Judy Loomis <hoodie.judy@xxxxxxxxx> 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
