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
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? |