From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Triggers
Date: Tue, 22 Nov 2005 16:44:35 +0100
User-Agent: KMail/1.8.1
References: <Pine.LNX.4.44.0511221825001.24697-100000@matrix.gatewaynet.com>
In-Reply-To: <Pine.LNX.4.44.0511221825001.24697-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200511221644.35656.leif@solumslekt.org>

On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
>O Leif B. Kristensen =DD=E3=F1=E1=F8=E5 =F3=F4=E9=F2 Nov 22, 2005 :
>> I'm trying to understand triggers. I have read the documentation in
>> the manual as well as the few pages in the Douglas book about the
>> subject, but I don't see how to implement a trigger that simply
>> updates a 'last_edit' date field in my 'persons' table whenever I do
>> an insert or update into my 'participants' table; that is a trigger
>> that basically does an "UPDATE persons SET 'last_edit' =3D NOW() WHERE
>> persons.person_id =3D participants.person_fk". Is that even possible?
>
>smth like:
>foodb=3D# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
>foodb'# BEGIN
>foodb'# UPDATE dad set lastedit=3Dnow() where id=3Dnew.dadid;
>foodb'# RETURN new;
>foodb'# END;
>foodb'# '
>foodb-# LANGUAGE plpgsql;
>CREATE FUNCTION
>foodb=3D# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> EACH ROW EXECUTE PROCEDURE upd_dad();

leif=3D> create or replace function update_last_edit() returns trigger as=20
$$
leif$> begin
leif$> update persons set last_edit=3Dnow() where person_id=3Dnew.person_fk;
leif$> return new;
leif$> end;
leif$> $$ language plpgsql;
CREATE FUNCTION
leif=3D> create trigger update_last_edit after insert or update on=20
participants
leif-> for each row execute procedure update_last_edit();
CREATE TRIGGER
leif=3D> insert into participants (participant_id,person_fk) values (1,1);
ERROR:  record "new" has no field "last_edit"
CONTEXT:  PL/pgSQL function "process_last_edited" line 2 at assignment
leif=3D>=20

What am I missing?
=2D-=20
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE