Re: Triggers on columns

On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote:
>> On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> > The SQL standard specifies that a trigger is fired if the column is
>> > mentioned in the UPDATE statement, independent of whether the value is
>> > actually changed through the update.
>>
>> That is thorougly bizarre, IMO.
>
> Well, if you find that bizarre, consider the existing behavior: Why
> should an ON UPDATE row trigger fire when none of the values of the
> row's columns actually change?  I think if you read
>
> TRIGGER ON UPDATE
>
> as
>
> TRIGER ON UPDATE OF <all columns>
>
> then it makes some sense.

Not to me. I use triggers to maintain database invariants, such as:

CREATE TABLE foo (id serial, name varchar, number_of_bars integer not
null default 0, primary key (id));
CREATE TABLE bar (id serial, foo_id integer not null references foo (id));

By setting up INSERT, UPDATE, and DELETE triggers on bar, I can
maintain the invariant that number_of_bars for each foo is in fact the
number of bars where foo_id is the id of that foo. However, in order
to suppress unnecessary updates to the foo table, I have to have the
update trigger check whether OLD.foo_id = NEW.foo_id before it does
anything.

If TRIGGER ON UPDATE OF foo_id means whether the value actually
changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id
means whether the column was present in the update list, then it
doesn't. Perhaps there are some use cases where we can be certain
that we only care about whether the value was in the update list, and
not whether it was changed, but off the top of my head it seems like
0% of mine would fall into that category.

It also seems to me logically inconsistent that we would expose this
information via the CREATE TRIGGER interface but not to the trigger
function itself. From within the function, you can compare NEW and
OLD, but you get no visibility into which columns were actually
updated. And apparently now from within CREATE TRIGGER we'll have
just the opposite. Blech...

By the way, I completely agree that it would be useful to have a way
to suppress triggers from firing when no columns were actually
modified. But I also wouldn't argue that should be the only available
behavior. Sometimes it's useful to schedule a no-op update explicitly
for the purpose of firing triggers.

...Robert