Обсуждение: Triggers
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? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE
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(); -- -Achilleus
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
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
Try: create or replace function update_last_edit() returns trigger as $$ begin new.last_edit=now(); return new; end; $$ language plpgsql; On 11/22/05, Leif B. Kristensen <leif@solumslekt.org> wrote: > 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 >
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 >
<p>On Tuesday 22 November 2005 18:07, Achilleus Mantzios wrote:<p>>O Leif B. Kristensen ?????? ???? Nov 22, 2005 :<p>>>What am I missing?<p>><p>>apparently some forgotten process_last_edited() function.<p><p>Yes -- an earlierattempt at the same thing ...<p><p>I finally managed to create my first trigger:<p><p>CREATE OR REPLACE FUNCTION update_last_edit()RETURNS TRIGGER AS $$<p>BEGIN<p> UPDATE persons SET last_edit=NOW() WHERE person_id=NEW.person_fk;<p> RETURNNEW;<p>END;<p>$$ LANGUAGE plpgsql;<p><p>CREATE TRIGGER update_last_edit <p>BEFORE INSERT OR UPDATE ON participants<p>FOR EACH ROW EXECUTE PROCEDURE update_last_edit();<p><p>And it's even working as it should :-)<p><p>Thanks,guys.<p>-- <p>Leif Biberg Kristensen | Registered Linux User #338009<p>http://solumslekt.org/ | Cruisingwith Gentoo/KDE<p>
O Neil Saunders έγραψε στις Nov 22, 2005 : > And change AFER INSERT to BEFORE INSERT 1) it doesnt make any difference since we are updating a different table than the trigger's one 2) Your email text comes really garbled > -- -Achilleus
On 11/23/2005 3:44 AM, Achilleus Mantzios wrote: > O Neil Saunders έγραψε στις Nov 22, 2005 : > >> And change AFER INSERT to BEFORE INSERT > > 1) it doesnt make any difference since we are updating > a different table than the trigger's one In this particular case it doesn't. In general, another BEFORE trigger can still modify the new row, so your trigger might not see the final values in NEW. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #