Re: Triggers on columns - Mailing list pgsql-hackers

On Thu, 3 Sep 2009, Robert Haas wrote:

> On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut<peter_e@gmx.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@gmx.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.

A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP 
as instance.

>
> ...Robert

-- 
Guillaume (ioguix) de Rorthais