Re: Trigger help - updates to column data with null values

[Greg Fischer <greg@xxxxxxxxxxx>]

NICE!!! Worked beautifully! I figured NULL's were not evaluating as a value, but I didn't have the correct operators:

these constructs effectively act as though null were a normal data value, rather than “unknown”.

I guess I wasn't reading the right part of the manuals! LOL!  Thanks so much!

Greg Fischer
www.1stbyte.com

On Tue, Mar 2, 2010 at 8:07 PM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote:

Greg Fischer wrote:
> Hello all!
> I do my best to read and google my way around issues, but I seem to be
> missing something. Probably simple too.
>
> So I have a trigger function, in which I'd like to check if a particular
> column has changed.  It works great, unless either the OLD or NEW values
> are NULL.  The purpose is to create a audit/log of the record.  I prefer
> to only test for certain columns, not the whole table.
>
>
> CREATE OR REPLACE FUNCTION tr_employees_bu()
>   RETURNS trigger AS
> $BODY$
> begin
> if (new.trainingdate <> old.trainingdate) then

Instead, try the test:

  new.trainingdate IS DISTINCT FROM old.trainingdate

See the manual for 'IS DISTINCT FROM'.

--
Craig Ringer