Thread: Trigger help - updates to column data with null values
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
insert into log_employees (employeeid,
source, datecreated,
createdby, oldvalue,newvalue)
values
(old.employeeid,'trainingdate',
now(),new.updatedby,
cast(old.codetrainingdate as varchar),
cast(new.codetrainingdate as varchar));
end if;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
I tried playing with "coalesce" and "nullif" but didn't get anywhere with those. I think the "if" expression is not right.
Thanks for any help!
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
NICE!!! Worked beautifully! I figured NULL's were not evaluating as a value, but I didn't have the correct operators:
Greg Fischer
www.1stbyte.com
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@postnewspapers.com.au> wrote:
Greg Fischer wrote:Instead, try the test:
> 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
new.trainingdate IS DISTINCT FROM old.trainingdate
See the manual for 'IS DISTINCT FROM'.
--
Craig Ringer