Trigger help - updates to column data with null values

From: Greg Fischer <greg(at)1stbyte(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Trigger help - updates to column data with null values
Date: 2010-03-03 01:20:01
Message-ID: c16a72fa1003021720h7d5383a9v6500de63c197becb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
www.1stbyte.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ozz Nixon 2010-03-03 01:30:30 SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
Previous Message Tom Lane 2010-03-03 00:16:53 Re: disable triggers isolated to transaction only?