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? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
