Skip site navigation (1) Skip section navigation (2)

Re: Creating Functions & Triggers

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Creating Functions & Triggers
Date: 2004-03-21 18:05:30
Message-ID: 20040321180530.GA18007@www.sirfsup.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, Mar 21, 2004 at 10:21:57AM -0700, Kevin Lohka wrote:
> Thanks for the help Tom & Markus I've got it now.
so did I so here is my version:

drop table email;
drop trigger email_mod_date;
drop function email_mod_date();
create table email ( 
id serial not null primary key,
email character varying(100), 
name character varying(100), 
m_date date, 
m_by_user character varying(100));

 CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
    BEGIN
          new.m_date = current_date;
		new.m_by_user = current_user;
	    RETURN new; 
    END;
 'LANGUAGE 'plpgsql';
 
 
 
 CREATE TRIGGER email_mod_date_trigger
 BEFORE UPDATE
 ON email
 FOR EACH ROW
 EXECUTE PROCEDURE email_mod_date();

 insert into email (email,name) values ('email1','name1');
 insert into email (email,name) values ('email2','name2');
 insert into email (email,name) values ('email3','name3');
select * from email;
update email set email='email1_new' where name='name1';
select * from email;

> 
> Kevin Lohka
> 
> On Sunday, March 21, 2004, at 09:18 AM, Tom Lane wrote:
> 
> >Markus Bertheau <twanger(at)bluetwanger(dot)de> writes:
> >>The trigger is probably recursively being called.
> >
> >Well, of course.  Every UPDATE causes another UPDATE, which queues
> >another trigger firing.  What else would you expect but an infinite
> >loop?
> >
> >The correct way to do this is illustrated in the plpgsql trigger
> >example at the bottom of this page:
> >http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
> >You use a BEFORE trigger and alter the NEW record before it gets
> >written.
> >
> >AFTER triggers are not intended for modifying data in the record they
> >are fired for --- it's too late for that.  (Even if you avoid the
> >infinite loop by testing whether you really need to do another UPDATE
> >or not, it's still ridiculously inefficient to force another cycle of
> >UPDATE when you could just as easily have gotten it right beforehand.)
> >Usually people use AFTER triggers for end-of-command consistency
> >checking or for propagating information to other tables.
> >
> >			regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
joe speigle
www.sirfsup.com

In response to

pgsql-novice by date

Next:From: Mihai TanasescuDate: 2004-03-21 18:29:38
Subject: Re: Simple list tables question
Previous:From: Kevin LohkaDate: 2004-03-21 17:21:57
Subject: Re: Creating Functions & Triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group