Re: Triggers

And change AFER INSERT to BEFORE INSERT

On 11/22/05, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> O Leif B. Kristensen έγραψε στις Nov 22, 2005 :
>
> > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
> > >O Leif B. Kristensen έγραψε στις 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' = NOW() WHERE
> > >> persons.person_id = participants.person_fk". Is that even possible?
> > >
> > >smth like:
> > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
> > >foodb'# BEGIN
> > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
> > >foodb'# RETURN new;
> > >foodb'# END;
> > >foodb'# '
> > >foodb-# LANGUAGE plpgsql;
> > >CREATE FUNCTION
> > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> > > EACH ROW EXECUTE PROCEDURE upd_dad();
> >
> > leif=> create or replace function update_last_edit() returns trigger as
> > $$
> > leif$> begin
> > leif$> update persons set last_edit=now() where person_id=new.person_fk;
> > leif$> return new;
> > leif$> end;
> > leif$> $$ language plpgsql;
> > CREATE FUNCTION
> > leif=> create trigger update_last_edit after insert or update on
> > participants
> > leif-> for each row execute procedure update_last_edit();
> > CREATE TRIGGER
> > leif=> 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=>
> >
> > What am I missing?
> >
>
> apparently some forgotten process_last_edited() function.
>
> --
> -Achilleus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>