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

Re: Creating Functions & Triggers

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Markus Bertheau <twanger(at)bluetwanger(dot)de>
Cc: Kevin Lohka <klohka(at)aboutfacedata(dot)ab(dot)ca>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Creating Functions & Triggers
Date: 2004-03-21 16:18:29
Message-ID: 25742.1079885909@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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: Tom LaneDate: 2004-03-21 16:24:50
Subject: Re: Simple list tables question
Previous:From: Mihai TanasescuDate: 2004-03-21 12:10:40
Subject: Simple list tables question

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