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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
