Re: trigger function in plpgsql (newbie)

Some general advice: often you will need both a BEFORE and an AFTER trigger
(split your logic up between the two) in order to get triggers right. If your
rows need a BEFORE trigger (they are constrained), then they often will need
an AFTER trigger (they are bound) too. The BEFORE trigger is to do mainly
just the constraint decision about if the change should be allowed or not,
and the AFTER trigger takes bound action as a result of when a change
actually occurs. The RAISE EXCEPTION plpgsql statement will be especially
useful in the BEFORE trigger when you need to veto (abort/rollback) the
proposed change. The AFTER trigger can also RAISE EXCEPTION to undo
everything if needed.

Robert

On Sunday 28 October 2001 08:52 am, Gunnar Lindholm wrote:
> Hello. I'm a total newbie when it comes to plpgsql and trigger functions,
> but here is my code that does not work
>
> create table T1 (
> ID integer primary key,
> v1 real );
>
> create table T2 (
> RID integer references T1,
> when date,
> v2 real,
> v1timesv2 real );
>
> I whish to do the following:
> Whenever I insert something into T2, I insert when and v2, I also wish to
> calculate the value v1timesv2 and store it in the table. (the value of v1
> changes over time). I wish to do this as a trigger function. I really don't
> know how to do this with a plpgsql function, here is a futile attempt.
>
> create function func_cal() RETURNS OPAQUE AS '
> BEGIN
> NEW.v1timesv2 := NEW.v2 * (select T1.v1 from T1 WHERE NEW.RID=T1.ID);
> RETURN NEW;
> END; '
> LANGUAGE 'plpgsql';
>
> create trigger trigger_happy AFTER INSERT ON T2
> for each ROW EXECUTE PROCEDURE func_cal();
>
> I guess you laugh at this, but I have not found any really good
> documentation about this (or am I just stupid?) so please tell me what I do
> wrong.
>
> By the way, in the name you write in the create trigger statement, when I
> look at the man page for create_trigger it says
> "name The name of an existing trigger."
> Eh... I thought I was creating a NEW trigger... any comments on this?
>
> TIA,
> Gunnar.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org