Re: A history procedure that prevents duplicate entries

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.

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

> INSERT INTO history.radical
> (rad_id, rad_char, rad_name)
> VALUES
> (hist_radical.rad_id, hist_radical.rad_char,
> hist_radical.rad_name);
> RETURN NULL;
> END;$$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR
> EACH ROW EXECUTE PROCEDURE history_radical();

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a87e8d010131556343596!