Re: Creating Functions & Triggers

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@bluetwanger.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