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

Re: Creating Functions & Triggers

From: Kevin Lohka <klohka(at)aboutfacedata(dot)ab(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Markus Bertheau <twanger(at)bluetwanger(dot)de>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Creating Functions & Triggers
Date: 2004-03-21 17:21:57
Message-ID: 41D67A4B-7B5C-11D8-BABB-000A95728606@aboutfacedata.ab.ca (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks for the help Tom & Markus I've got it now.

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


In response to

Responses

pgsql-novice by date

Next:From: joseph speigleDate: 2004-03-21 18:05:30
Subject: Re: Creating Functions & Triggers
Previous:From: Tom LaneDate: 2004-03-21 16:24:50
Subject: Re: Simple list tables question

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