Update Trigger Inconsistency with 7.1?

From: "Gregory Wood" <gregw(at)com-stock(dot)com>
To: "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org>
Subject: Update Trigger Inconsistency with 7.1?
Date: 2001-01-26 17:23:02
Message-ID: 000d01c087bc$a6226fd0$7889ffcc@comstock.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We recently upgraded our development server to 7.1 and I believe I've
noticed an inconsistency with how update triggers behave on version 7.1
versus 7.0. Since I'm not sure which should be the expected behavior I have
no idea if it is a bug or not. Here is the situation:

I have a table in which on field (two actually) *needs* to be updated every
time, *even* if the new value is the same as the old value. For example:
UPDATE Foo SET UpdateOptional='something', UpdateRequired='updated' WHERE
Other='value' would work, but UPDATE Foo SET UpdateOptional='something'
WHERE Other='value' would not work, because no value was explicitly given
for UpdateRequired.

To do this I created a trigger that would raise an exception "IF
new.UpdateRequired ISNULL". In 7.0 this would work because
new.UpdateRequired seemed to be NULL unless it was specified (I cannot test
this any longer because I don't have a 7.0 server available). In 7.1 this
behavior seemed to change: new.UpdateRequired is now equal to the
old.UpdateRequired value if no new value is specified.

Which behavior should I expect... and how else can I tell if a value has
been given by an UPDATE statement (keeping in mind that the value may or may
not change)?

Greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Camm Maguire 2001-01-26 17:44:31 Calculated values
Previous Message Mikheev, Vadim 2001-01-26 17:12:09 RE: vacuum