Re: A history procedure that prevents duplicate entries

On 16 Aug 2009, at 17:38, Madison Kelly wrote:

>> Besides that, you don't need the SELECT statement or the RECORD-
>> type variable as the data you need is already in the NEW and OLD
>> records. But, you only have an OLD record when your trigger fired
>> from an UPDATE, so you need to check whether your trigger fired
>> from INSERT or UPDATE.
>> So, what you need is something like:
>> IF TG_OP = 'INSERT' THEN hist_radical := NEW;
>> ELSE -- TG_OP = 'UPDATE'
>> hist_radical := OLD;
>> END IF;
>> INSERT INTO history.radical
>> (rad_id, rad_char, rad_name)
>> VALUES
>> (hist_radical.rad_id, hist_radical.rad_char,
>> hist_radical.rad_name);
>
> To help me improve my understanding of procedures, how would this
> prevent an UPDATE from creating a new entry in the history schema
> when all the column values are the same as the last entry in history?

It doesn't, as it wasn't entirely clear to me how you wanted it to
behave.

To prevent duplicate history entries from updates you would need to
compare the values of NEW and OLD and return if they're equal. In 8.4
that's as simple as checking that NEW IS DISTINCT FROM OLD, but in
earlier versions it's a bit more involved. There was a discussion
about this very topic here recently.

Alban Hertroys

--
Screwing up is the correct approach to attaching something to the
ceiling.

!DSPAM:737,4a8bd41d10131434511488!