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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2004-03-21 16:24:50 Re: Simple list tables question
Previous Message Mihai Tanasescu 2004-03-21 12:10:40 Simple list tables question