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

Re: trigger self recursion

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Sim Zacks <sim(at)compulab(dot)co(dot)il>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: trigger self recursion
Date: 2005-09-28 11:27:52
Message-ID: 20050928112752.GG2436@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Sep 28, 2005 at 02:20:57PM +0200, Sim Zacks wrote:
> Basically I have a table that is not fully normalized. When the user
> updates a field that has a "duplicate" I would like it to update those
> duplicate rows as well.
> The code is very straightforward.
> 
>     Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
>     and f4=new.f4 and f5=new.f5
> 
> Where table1 is the original table being updated.

Well, the solution seems to me to be:

Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
and f4=new.f4 and f5=new.f5 and (f1<>new.f1 or f2<>new.f2 or f3<>new.f3);

I.e., say what you mean. You don't want to update rows that already
have the right values.

> In SQL Server/Sybase, for example, a trigger is only fired per table
> once.

Once per row I assume. If you're updating multiple rows you want the
trigger to apply to each change. Seems like an arbitrary restriction to
me.
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-general by date

Next:From: Sim ZacksDate: 2005-09-28 11:41:29
Subject: trigger self recursion
Previous:From: Richard HuxtonDate: 2005-09-28 11:13:27
Subject: Re: trigger self recursion

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