Re: A history procedure that prevents duplicate entries

Alban Hertroys wrote:
> On 16 Aug 2009, at 4:24, Madison Kelly wrote:
>
>> Hi all,
>
> ...
>
>> CREATE FUNCTION history_radical() RETURNS "trigger"
>>     AS $$
>>     DECLARE
>>         hist_radical RECORD;
>>     BEGIN
>>         SELECT INTO hist_radical * FROM public.radical WHERE
>> rad_id=new.rad_id;
>
> I assume you mean to only select an existing record here in case the
> trigger is fired on an update? You are in fact always selecting at least
> one record here because this is called from an AFTER INSERT OR UPDATE
> trigger; the record has already been inserted or updated, so the select
> statement will find the new (version of) the record.
>
> I'm also not entirely sure what the value is of calling your procedure
> on INSERT. If I interpreted you correctly the same data would be added
> to the history the first time it gets updated (except for the different
> timestamp and history id of course). I'd probably just call this
> procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT
> I'd make it clear there was no data before that history entry, for
> example by filling the record with NULL values or by adding a column for
> the value of TG_OP to the history table.

The INSERT is there mainly for my convenience. If I am going to the
history schema to get data, it's convenient to know that is has a
complete copy of the data in the public schema, too.

> 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);
>
>
> Alban Hertroys

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?

Thanks!!

Madi